<?php

namespace app\modules\Order\controllers;

use Yii;
use app\modules\Product\models\Product;
use app\modules\Order\models\Order;
use app\modules\Order\models\OrderSearch;
use app\modules\Order\models\OrderRoom;
use app\modules\Finance\models\FinanceSearch;
use app\modules\Finance\models\FinanceOrder;
use yii\db\Query;
use app\models\Logs;
use yii\web\Controller;
use yii\web\NotFoundHttpException;
use yii\filters\VerbFilter;
use yii\filters\AccessControl;

/**
 * Word
 */
class WordController extends Controller
{

    public function behaviors()
    {
        return [
            'verbs' => [
                'class' => VerbFilter::className(),
                'actions' => [
                //  'delete' => ['post'],
                ],
            ],
            'access' => [
                'class' => AccessControl::className(),
                'rules' => [
                    [
                        'allow' => true,
                        'roles' => ['@'],
                    ],
                ],
            ],
        ];
    }

 
 	//文件打包
    public function actionPack(){
        //获取所有未过期的航期
        $today = date("Y-m-d");
        $products = Product::find()->where(['>=','go_time',$today])->andWhere(['status'=>1])->all();
        //$host = "http://longyu.huoliwu.cn";
        $host = yii::$app->request->getHostInfo();
        if(empty($products)){
            exit("没有船期可打包");
        }
        $file_arr = [];
        foreach($products as $model){

            $_url_1 = sprintf("%s/product/product/word?id=%d&type=1",$host,$model->id);
            $_url_2 = sprintf("%s/product/product/word?id=%d&type=2",$host,$model->id);
            $xdoc_1 = urlencode($_url_1);
            $xdoc_2 = urlencode($_url_2);
            $filename_1 = sprintf("%s(%s)(同业价格).docx",$model->cruise->name,$model->go_time);
            $filename_1 = iconv("utf-8","gbk//ignore",$filename_1);
            $filename_2 = sprintf("%s(%s)(直客价格).docx",$model->cruise->name,$model->go_time);
            $filename_2 = iconv("utf-8","gbk//ignore",$filename_2);
            $url_1 = sprintf("http://www.xdocin.com/xdoc?_func=run&_format=docx&_xdoc=%s",$xdoc_1);
            $url_2 =sprintf("http://www.xdocin.com/xdoc?_func=run&_format=docx&_xdoc=%s",$xdoc_2);
            $dir = 'uploads/doc';
            
            $res1 = $this->getFile($url_1,$dir,$filename_1,0);
            $res2 = $this->getFile($url_2,$dir,$filename_2,0);

            $file_arr[] = $res1;
            $file_arr[] = $res2;

        }

        $zip = new \ZipArchive;//打包
        $file = $filename = "所有航期价格Word文件.zip";
        $filename = sprintf("%s/web/uploads/zip/%s",Yii::$app->basePath,$filename);
        $filename = iconv("utf-8","gbk//ignore",$filename); //中文乱码
        $res = $zip->open($filename, \ZipArchive::OVERWRITE);//如果打包文件有就覆盖
        if($res === TRUE) {
            foreach ($file_arr as $val) {
              
                $zip->addFile($val['save_path'],$val['file_name']); 
            }
            $zip->close();
        }   

        header ( "Cache-Control: max-age=0" );
        header ( "Content-Description: File Transfer" );
        header ( 'Content-disposition: attachment; filename=' .$file); // 文件名
        header ( "Content-Type: application/zip" ); // zip格式的
        header ( "Content-Transfer-Encoding: binary" ); // 告诉浏览器，这是二进制文件
        header ( 'Content-Length: ' . filesize ( $filename ) ); // 告诉浏览器，文件大小
        @readfile ( $filename );//输出文件;
       
       
    }


    private function getFile($url,$save_dir='',$filename='',$type=0){
        if(trim($url)==''){
           return false;
        }
        if(trim($save_dir)==''){
           $save_dir='./';
        }
        if(0!==strrpos($save_dir,'/')){
           $save_dir.='/';
        }
        //创建保存目录
        if(!file_exists($save_dir)&&!mkdir($save_dir,0777,true)){
            return false;
        }
         //获取远程文件所采用的方法
        if($type){
          $ch=curl_init();
          $timeout=5;
          curl_setopt($ch,CURLOPT_URL,$url);
          curl_setopt($ch,CURLOPT_RETURNTRANSFER,1);
          curl_setopt($ch,CURLOPT_CONNECTTIMEOUT,$timeout);
          $content=curl_exec($ch);
          curl_close($ch);
        }else{
          ob_start();
          readfile($url);
          $content=ob_get_contents();
          ob_end_clean();
        }
        $size=strlen($content);
        //文件大小
        $fp2=@fopen($save_dir.$filename,'w');
        fwrite($fp2,$content);
        fclose($fp2);
        unset($content,$url);
        return array('file_name'=>$filename,'save_path'=>$save_dir.$filename);
    }

    //确认单
    public function actionOldDownExcel($id,$begin_date,$end_date)
    {
        
        if(empty($id)){
            return false;
        }
        $order = Order::findOne($id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('确认单');
        $sheet->getDefaultStyle()->getFont()->setSize(9)->setName('微软雅黑');
        $sheet->getDefaultStyle()->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getDefaultStyle()->getFill()->getStartColor()->setARGB('C6C3C6');

        $sheet->getColumnDimension('A')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(20);// 设置宽度
        $sheet->getRowDimension(1)->setRowHeight(15);
        $sheet->getRowDimension(2)->setRowHeight(15);
        $sheet->getRowDimension(3)->setRowHeight(15);
        $sheet->getRowDimension(4)->setRowHeight(30);

        $sheet->mergeCells('A1:C3'); 
        $sheet->mergeCells('D2:H3');

        $sheet->mergeCells('A4:H4');
        $sheet->mergeCells('B5:E5');
        $sheet->mergeCells('B6:E6');
        $sheet->mergeCells('G5:H5');
        $sheet->mergeCells('G6:H6');
        $sheet->mergeCells('A8:H8');


        $sheet->getStyle("D2")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle("D2")->getFont()->setSize(14)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->setCellValue("D2","龙旅在线国际旅行社有限公司天津第二分公司");
        $sheet->getStyle("A4:H4")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle("A4:H4")->getFont()->setSize(11)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->setCellValue("A4","邮轮产品同业预定确认单及付款通知书");

        $sheet->setCellValue("A5","客户名称：");
        $sheet->setCellValue("B5",$order->company);
        $sheet->setCellValue("F5","制单日期：");
        $sheet->setCellValue("G5",date("Y年m月d日")); 
        $sheet->setCellValue("A6","联 系 人 ：");
        $sheet->setCellValue("B6",$order->username);
        $sheet->setCellValue("F6","制 单 人：");
        $sheet->setCellValue("G6",$order->member->profile->name);

        $sheet->getStyle("A8")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle("A8")->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->setCellValue("A8","产 品 信 息");
        $sheet->getStyle("A8")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle("A8")->getFill()->getStartColor()->setARGB('FFE6CC');

        $sheet->setCellValue("A9","船名");
        $sheet->setCellValue("D9","出发日期");
        $sheet->setCellValue("G9","天数");
        $sheet->getStyle("A9")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle("A9")->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->getStyle("D9")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle("D9")->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->getStyle("G9")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle("G9")->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');

        $sheet->mergeCells('B9:C9');
        $sheet->mergeCells('E9:F9');
        $sheet->setCellValue("B9",$order->product->cruise->name);
        $sheet->setCellValue("E9",date("Y年m月d日",strtotime($order->product->go_time)));
        $sheet->setCellValue("H9",$order->product->day.'天');
        $i = 0;
        if($order->product->stock == 1){
            foreach($order->room as $key=>$room){
                $sheet->mergeCells(sprintf("B%d:C%d",$key+10,$key+10));
                $sheet->mergeCells(sprintf("D%d:H%d",$key+10,$key+10));
                //获得入住人员
                $customers = '';
                $bool = true;
                foreach($room->orderRoomCustomer as $cus){
                    $customers .= $cus->orderCustomer->first_name.$cus->orderCustomer->last_name;
                    if($cus->orderCustomer->phone && $bool){
                        $bool = false;
                        $customers .='('.$cus->orderCustomer->phone.')';
                    }
                    $customers .='、';
                }
                
                //$customers = rtrim($customers,'、');
                
                $sheet->setCellValue(sprintf("B%d",$key+10),$room->productRoom->room->name.$room->productRoom->room->code.$room->productRoom->people.'人间');
                $sheet->setCellValue(sprintf("D%d",$key+10),$customers);
            }
        }elseif($order->product->stock == 2){
            $t = json_decode($order->product->rooms, true);
            foreach($order->room as $key=>$room){
                $sheet->mergeCells(sprintf("B%d:C%d",$key+10,$key+10));
                $sheet->mergeCells(sprintf("D%d:H%d",$key+10,$key+10));
                //获得入住人员
                $customers = '';
                $bool = true;
                foreach($room->orderRoomCustomer as $cus){
                    $customers .= $cus->orderCustomer->first_name.$cus->orderCustomer->last_name;
                    if($cus->orderCustomer->phone && $bool){
                        $bool = false;
                        $customers .='('.$cus->orderCustomer->phone.')';
                    }
                    $customers .='、';
                }

                //$customers = rtrim($customers,'、');
                
                $sheet->setCellValue(sprintf("B%d",$key+10),$t[$room->room_id]['name']);
                $sheet->setCellValue(sprintf("D%d",$key+10),$customers);
            }
        }
        $num = count($order->room);
        if($num > 1){
            $i = $i+$num - 1;
        }
        $sheet->mergeCells(sprintf("A10:A%d",$i+10));  
        $sheet->setCellValue("A10","出行人信息");
        $sheet->getStyle("A10")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle("A10")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle("A10")->getFill()->getStartColor()->setARGB('DCEEF5');

        $sheet->mergeCells(sprintf("A%d:H%d",$i+11,$i+11));

        $sheet->mergeCells(sprintf("A%d:H%d",$i+12,$i+12));
        $sheet->setCellValue(sprintf("A%d",$i+12),"收 款 项 目 明 细");
        $sheet->getStyle(sprintf("A%d",$i+12))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle(sprintf("A%d",$i+12))->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->getStyle(sprintf("A%d",$i+12))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle(sprintf("A%d",$i+12))->getFill()->getStartColor()->setARGB('F9D0E2');

        $sheet->mergeCells(sprintf("A%d:D%d",$i+13,$i+13));
        $sheet->mergeCells(sprintf("G%d:H%d",$i+13,$i+13));
        $sheet->setCellValue(sprintf("A%d",$i+13),"项目类型");
        $sheet->setCellValue(sprintf("E%d",$i+13),"数量");
        $sheet->setCellValue(sprintf("F%d",$i+13),"单价");
        $sheet->setCellValue(sprintf("G%d",$i+13),"小计");
        $sheet->getStyle(sprintf("A%d:H%d",$i+13,$i+13))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $ii = 0;
        if($order->product->stock == 1){
            foreach($order->room as $key=>$room){
                $sheet->mergeCells(sprintf("A%d:D%d",$key+$i+14,$key+$i+14));
                $sheet->mergeCells(sprintf("G%d:H%d",$key+$i+14,$key+$i+14));
                $sheet->setCellValue(sprintf("A%d",$key+$i+14),$room->productRoom->room->name.$room->productRoom->room->code. $room->productRoom->people.'人间');
                $sheet->setCellValue(sprintf("E%d",$key+$i+14),$room->num);
                
                $sheet->setCellValue(sprintf("F%d",$key+$i+14),$room->price/$room->num);
               
                $sheet->setCellValue(sprintf("G%d",$key+$i+14),"=".sprintf("E%d",$key+$i+14)."*".sprintf("F%d",$key+$i+14));
                $ii++;
            }
        }elseif($order->product->stock == 2){
            $t = json_decode($order->product->rooms, true);
            foreach($order->room as $key=>$room){
                $sheet->mergeCells(sprintf("A%d:D%d",$key+$i+14,$key+$i+14));
                $sheet->mergeCells(sprintf("G%d:H%d",$key+$i+14,$key+$i+14));
                $sheet->setCellValue(sprintf("A%d",$key+$i+14),$t[$room->room_id]['name']);
                $sheet->setCellValue(sprintf("E%d",$key+$i+14),$room->num);
               
                $sheet->setCellValue(sprintf("F%d",$key+$i+14),$room->price/$room->num);
               
                $sheet->setCellValue(sprintf("G%d",$key+$i+14),"=".sprintf("E%d",$key+$i+14)."*".sprintf("F%d",$key+$i+14));
                $ii++;
            }
        }

        $iii = $ii+$i;
        foreach($order->prices as $key=>$op){
            $sheet->mergeCells(sprintf("A%d:D%d",$key+$iii+14,$key+$iii+14));
            $sheet->mergeCells(sprintf("G%d:H%d",$key+$iii+14,$key+$iii+14));
            $sheet->setCellValue(sprintf("A%d",$key+$iii+14),$op->typeObj->name);
            $sheet->setCellValue(sprintf("E%d",$key+$iii+14),$op->num);
            $sheet->setCellValue(sprintf("F%d",$key+$iii+14),$op->price);
            $sheet->setCellValue(sprintf("G%d",$key+$iii+14),"=".sprintf("E%d",$key+$iii+14)."*".sprintf("F%d",$key+$iii+14));
            $ii++;
        }
        $i = $i+1;
        $sheet->mergeCells(sprintf("A%d:D%d",$ii+$i+13,$ii+$i+13));
        $sheet->mergeCells(sprintf("G%d:H%d",$ii+$i+13,$ii+$i+13));
        $sheet->setCellValue(sprintf("A%d",$ii+$i+13),sprintf("优惠金额(%s)",$order->price_desc));
        $sheet->setCellValue(sprintf("E%d",$ii+$i+13),1);
        $sheet->setCellValue(sprintf("F%d",$ii+$i+13),-($order->sale_price));
        $sheet->setCellValue(sprintf("G%d",$ii+$i+13),"=".sprintf("E%d",$ii+$i+13)."*".sprintf("F%d",$ii+$i+13));
        $sheet->getStyle(sprintf("A%d",$ii+$i+13))->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('FF0000');

        $sheet->getStyle(sprintf("E%d:H%d",$i+13,$ii+$i+13))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $ii++;
        $sheet->mergeCells(sprintf("A%d:D%d",$ii+$i+13,$ii+$i+13));
        $sheet->mergeCells(sprintf("G%d:H%d",$ii+$i+13,$ii+$i+13));
        $sheet->setCellValue(sprintf("A%d",$ii+$i+13),"舱房退扣");
        $sheet->setCellValue(sprintf("E%d",$ii+$i+13),1);
        $sheet->setCellValue(sprintf("F%d",$ii+$i+13),($order->refund_price));
        $sheet->setCellValue(sprintf("G%d",$ii+$i+13),"=".sprintf("E%d",$ii+$i+13)."*".sprintf("F%d",$ii+$i+13));
        $sheet->getStyle(sprintf("A%d",$ii+$i+13))->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('FF0000');

        $sheet->getStyle(sprintf("E%d:H%d",$i+13,$ii+$i+13))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $sheet->mergeCells(sprintf("A%d:F%d",$ii+$i+14,$ii+$i+14));
        $sheet->mergeCells(sprintf("G%d:H%d",$ii+$i+14,$ii+$i+14));
        $sheet->setCellValue(sprintf("A%d",$ii+$i+14),"费用合计");
        $sheet->setCellValue(sprintf("G%d",$ii+$i+14),"=SUM(".sprintf("G%d",$i+13).":".sprintf("G%d",$ii+$i+13).')');

        $sheet->getStyle(sprintf("A%d",$ii+$i+14))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle(sprintf("A%d",$ii+$i+14))->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');

        $sheet->getStyle(sprintf("G%d",$ii+$i+14))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle(sprintf("G%d",$ii+$i+14))->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
       

        $sheet->mergeCells(sprintf("A%d:H%d",$ii+$i+15,$ii+$i+15));
        $sheet->mergeCells(sprintf("A%d:H%d",$ii+$i+16,$ii+$i+16));
        $sheet->setCellValue(sprintf("A%d",$ii+$i+16),"旅 游 费 用 包 含 和 不 包 含 项 目");
        $sheet->getStyle(sprintf("A%d",$ii+$i+16))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle(sprintf("A%d",$ii+$i+16))->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->getStyle(sprintf("A%d",$ii+$i+16))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle(sprintf("A%d",$ii+$i+16))->getFill()->getStartColor()->setARGB('C9DAE9');
        $sheet->mergeCells(sprintf("A%d:C%d",$ii+$i+17,$ii+$i+17));
        $sheet->mergeCells(sprintf("D%d:H%d",$ii+$i+17,$ii+$i+17));
        $sheet->setCellValue(sprintf("A%d",$ii+$i+17),"费用包含");
        $sheet->setCellValue(sprintf("D%d",$ii+$i+17),"费用不包含");
        $sheet->getStyle(sprintf("A%d:H%d",$ii+$i+17,$ii+$i+17))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle(sprintf("A%d:H%d",$ii+$i+17,$ii+$i+17))->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');

        $html1 = $order->product->cost;
        $html1 = $this->htmlTostring($html1);

        $html2 = $order->product->nocost;
        $html2 = $this->htmlTostring($html2);
        

        $sheet->mergeCells(sprintf("A%d:C%d",$ii+$i+18,$ii+$i+18));
        $sheet->mergeCells(sprintf("D%d:H%d",$ii+$i+18,$ii+$i+18));
        $sheet->getRowDimension($ii+$i+18)->setRowHeight(150);
        $sheet->setCellValue(sprintf("A%d",$ii+$i+18),$html1);
        $sheet->getStyle(sprintf("A%d",$ii+$i+18))->getAlignment()->setWrapText(true);

        $sheet->getStyle(sprintf("A%d:H%d",$ii+$i+18,$ii+$i+18))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);

        $sheet->setCellValue(sprintf("D%d",$ii+$i+18),$html2);
        $sheet->getStyle(sprintf("D%d",$ii+$i+18))->getAlignment()->setWrapText(true);

        $sheet->mergeCells(sprintf("B%d:H%d",$ii+$i+19,$ii+$i+19));
        $sheet->getRowDimension($ii+$i+19)->setRowHeight(70);
        $sheet->setCellValue(sprintf("A%d",$ii+$i+19),"取消规则");
        $sheet->getStyle(sprintf("A%d",$ii+$i+19))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle(sprintf("A%d",$ii+$i+19))->getFont()->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');


        $html3 = $order->product->cancel;
        $html3 = $this->htmlTostring($html3);
        $sheet->setCellValue(sprintf("B%d",$ii+$i+19),$html3);
        $sheet->getStyle(sprintf("B%d:H%d",$ii+$i+19,$ii+$i+19))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
        $sheet->getStyle(sprintf("B%d",$ii+$i+19))->getAlignment()->setWrapText(true);

        $sheet->mergeCells(sprintf("A%d:H%d",$ii+$i+20,$ii+$i+20));

        $sheet->mergeCells(sprintf("A%d:B%d",$ii+$i+21,$ii+$i+21));
        $sheet->mergeCells(sprintf("C%d:D%d",$ii+$i+21,$ii+$i+21));
        $sheet->mergeCells(sprintf("E%d:F%d",$ii+$i+21,$ii+$i+21));
        $sheet->mergeCells(sprintf("G%d:H%d",$ii+$i+21,$ii+$i+21));
        $sheet->setCellValue(sprintf("A%d",$ii+$i+21),"以上确认无误后，请您于");
        $sheet->setCellValue(sprintf("C%d",$ii+$i+21),date("Y年m月d日 H:i",strtotime($begin_date)));
        $sheet->setCellValue(sprintf("E%d",$ii+$i+21),"前将盖章确认回传至我社，并于");
        $sheet->setCellValue(sprintf("G%d",$ii+$i+21),date("Y年m月d日 H:i",strtotime($end_date)));

        $sheet->getStyle(sprintf("C%d",$ii+$i+21,$ii+$i+21))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle(sprintf("G%d",$ii+$i+21,$ii+$i+21))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle(sprintf("C%d",$ii+$i+21))->getFont()->setUnderline(\PHPExcel_Style_Font::UNDERLINE_SINGLE)->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('FF0000');
        $sheet->getStyle(sprintf("G%d",$ii+$i+21))->getFont()->setUnderline(\PHPExcel_Style_Font::UNDERLINE_SINGLE)->setSize(9)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('FF0000');


        $sheet->mergeCells(sprintf("A%d:H%d",$ii+$i+22,$ii+$i+22));
        $sheet->setCellValue(sprintf("A%d",$ii+$i+22),"前向我社对公账户支付 船票全款 ，并请将水单发至我社，方可保留舱位。若逾期未付，会自动取消您的预定。");

        $sheet->mergeCells(sprintf("A%d:E%d",$ii+$i+23,$ii+$i+23));
        $sheet->mergeCells(sprintf("F%d:H%d",$ii+$i+23,$ii+$i+23));
        $sheet->getRowDimension($ii+$i+23)->setRowHeight(120);
        $sheet->setCellValue(sprintf("A%d",$ii+$i+23),"    我社对公账号如下：\n    账户名：龙旅在线国际旅行社有限公司天津第二分公司\n    账   号：9120 0501 0000 0204 70\n    开户行：中国邮政储蓄银行天津河西区支行");
        $sheet->getStyle(sprintf("A%d",$ii+$i+23))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->setCellValue(sprintf("F%d",$ii+$i+23),"    盖章：\n\n       2017年     月     日  ");
        $sheet->getStyle(sprintf("A%d",$ii+$i+23))->getAlignment()->setWrapText(true);
        $sheet->getStyle(sprintf("F%d",$ii+$i+23))->getAlignment()->setWrapText(true);

        $sheet->mergeCells(sprintf("A%d:H%d",$ii+$i+24,$ii+$i+24));

        $objDrawing = new \PHPExcel_Worksheet_Drawing();
        $objDrawing->setPath("./img/longlv.png");
        $objDrawing->setOffsetX(25);                       
        $objDrawing->setOffsetY(10); 
        $objDrawing->setHeight(43.5);
        $objDrawing->setWidth(155.5);
        $objDrawing->setCoordinates('A1');
        $objDrawing->setWorksheet($sheet);

        $objDrawing2 = new \PHPExcel_Worksheet_Drawing();
        $objDrawing2->setPath("./img/longlv2.png");
        $objDrawing2->setOffsetX(10);                       
        $objDrawing2->setHeight(43.5);
        $objDrawing2->setWidth(155.5);
        $objDrawing2->setCoordinates(sprintf("D%d",$ii+$i+23));
        $objDrawing2->setWorksheet($sheet);

        $styleArray = array(  
            'borders' => array(  
                'allborders' => array(  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框   
                    'color' => array('argb' => 'FFFFFF'),  
                ),  
            ),  
        );        
        $sheet->getStyle('A1:H7')->applyFromArray($styleArray);

        $styleArray1 = array(  
            'borders' => array(  
                'bottom' => array(  
                    'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的   
                    'color' => array('argb' => '0000D0'),  
                ),  
            ),  
        );      
        $styleArray2 = array(  
            'borders' => array(  
                'right' => array(  
                    'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的   
                    'color' => array('argb' => '0000D0'),  
                ),  
            ),  
        );   
        $styleArray3 = array(  
            'borders' => array(  
                'top' => array(  
                    'style' => \PHPExcel_Style_Border::BORDER_THICK,//细边框  
                    'color' => array('argb' => '0000D0'),  
                ),  
            ),  
        );   
        $styleArray4 = array(  
            'borders' => array(  
                'left' => array(  
                    'style' => \PHPExcel_Style_Border::BORDER_THICK,//细边框  
                    'color' => array('argb' => '0000D0'),  
                ),  
            ),  
        );   

        $styleArray5 = array(  
            'borders' => array(  
                'allborders' => array(  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框   
                    'color' => array('argb' => 'C6C3C6'),  
                ),  
            ),  
        );   

        

        $sheet->getStyle(sprintf("A%d:H%d",$ii+$i+17,$ii+$i+24))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle(sprintf("A%d:H%d",$ii+$i+17,$ii+$i+24))->getFill()->getStartColor()->setARGB('FFFFFF');

        $sheet->getStyle("A1:H7")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle("A1:H7")->getFill()->getStartColor()->setARGB('FFFFFF');

        $sheet->getStyle(sprintf("A9:H%d",$i+10))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle(sprintf("A9:H%d",$i+10))->getFill()->getStartColor()->setARGB('FFFFFF');

        $sheet->getStyle(sprintf("A10:A%d",$i+9))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle(sprintf("A10:A%d",$i+9))->getFill()->getStartColor()->setARGB('DCEEF5');

        $sheet->getStyle(sprintf("A%d:H%d",$i+12,$ii+$i+15))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle(sprintf("A%d:H%d",$i+12,$ii+$i+15))->getFill()->getStartColor()->setARGB('FFFFFF');

        $sheet->getStyle(sprintf("A9:H%d",$i+11))->applyFromArray($styleArray5);
        $sheet->getStyle(sprintf("A%d:H%d",$ii+$i+16,$ii+$i+24))->applyFromArray($styleArray5);
        $sheet->getStyle(sprintf("A%d:H%d",$i+13,$ii+$i+15))->applyFromArray($styleArray5);

        $sheet->getStyle(sprintf("A%d:H%d",$ii+$i+24,$ii+$i+24))->applyFromArray($styleArray1);
        $sheet->getStyle(sprintf("H%d:H%d",1,$ii+$i+24))->applyFromArray($styleArray2);
        $sheet->getStyle("A1:H1",1)->applyFromArray($styleArray3);
        $sheet->getStyle(sprintf("A1:A%d",$ii+$i+24))->applyFromArray($styleArray4);

        $sheet->getStyle(sprintf("G%d",$ii+$i+14))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle(sprintf("G%d",$ii+$i+14))->getFill()->getStartColor()->setARGB('FFFF00');


        $pageMargins = $sheet->getPageMargins();

        $margin = 1.78 / 2.54;   
        $marginright = 1 / 2.54;   

        $pageMargins->setTop($margin);       //上边距
        $pageMargins->setBottom($margin); //下
        $pageMargins->setLeft($margin);      //左
        $pageMargins->setRight($marginright);    //右
        $sheet->getPageSetup()->setFitToWidth('1');//自动填充到页面的宽度
        $sheet->getPageSetup()->setFitToHeight('1');//自动填充到页面的高度

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $file_name = sprintf("订单号：%s.xlsx",$order->code);

        header("Content-Type: application/force-download"); 
        header("Content-Type: application/octet-stream"); 
        header("Content-Type: application/download"); 
        header('Content-Disposition:inline;filename="'.$file_name.'"'); 
        header("Content-Transfer-Encoding: binary"); 
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
        header("Pragma: no-cache"); 
        $objWriter->save('php://output');   

    }

    //新确认单 易游 中青旅 
    public function actionDownExcel($id,$end_date,$tem_sheet=1){
        if(empty($id)){
            return false;
        }
        $order = Order::findOne($id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('确认单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('微软雅黑');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $sheet->getColumnDimension('A')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(35);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(8);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(8);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(9);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(9);// 设置宽度
        $sheet->getRowDimension(1)->setRowHeight(68);
        $sheet->mergeCells('A1:I1');
        $sheet->mergeCells('B2:I2');
        $sheet->mergeCells('B3:C3');
        $sheet->mergeCells('E3:F3');
        $sheet->mergeCells('H3:I3');
        $sheet->mergeCells('A4:I4');
        $sheet->mergeCells('B5:I5');
        $sheet->mergeCells('F6:G6');
        $sheet->mergeCells('H6:I6');
        if($tem_sheet == 2){
            $sheet->setCellValue("A1","中青旅控股股份有限公司 \n邮轮产品  确认单");
            $sheet->setCellValue("B3","中青旅控股股份有限公司");
        }else{
            $sheet->setCellValue("A1","易游天下国际旅行社（北京）有限公司\n游轮事业部  确认单");
            $sheet->setCellValue("B3","易游天下国际旅行社（北京）有限公司");
        }
        $sheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $sheet->getStyle("A1")->getFont()->setSize(16)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
      
        $sheet->setCellValue("A2","TO");
        $sheet->setCellValue("B2",$order->company.' '.$order->username);
        $sheet->setCellValue("A3","From");
        $sheet->setCellValue("D3","销售");
        $sheet->setCellValue("E3",$order->member->profile->name);
        $sheet->setCellValue("G3","电话");
        $sheet->setCellValue("H3","");
        $sheet->setCellValue("A4","费用明细");
        $sheet->setCellValue("A5","线路");
        $sheet->setCellValue("B5",date("Y年m月d日",strtotime($order->product->go_time)).' '.$order->product->name);
        $sheet->setCellValue("A6","序号");
        $sheet->setCellValue("B6","项目类型");
        $sheet->setCellValue("C6","间数");
        $sheet->setCellValue("D6","人数");
        $sheet->setCellValue("E6","客人姓名");
        $sheet->setCellValue("F6","单价");
        $sheet->setCellValue("H6","合计");

        $key = 0;
        if($order->product->stock == 1){
            foreach($order->room as $room){

                $customers = '';
                $orderRoomCustomer = $room->orderRoomCustomer;
                if($orderRoomCustomer){
                    foreach($orderRoomCustomer as $orc){
                        $name = $orc->orderCustomer->first_name.$orc->orderCustomer->last_name.'、';
                        $customers .= $name;
                    }
                }


                $sheet->mergeCells(sprintf("F%d:G%d",$key+7,$key+7));
                $sheet->mergeCells(sprintf("H%d:I%d",$key+7,$key+7));
                $sheet->setCellValue(sprintf("A%d",$key+7),$key+1);
                $sheet->setCellValue(sprintf("B%d",$key+7),$room->productRoom->room->name.$room->productRoom->room->code. $room->productRoom->people.'人间');

                $sheet->getStyle(sprintf("B%d",$key+7))->getAlignment()->setWrapText(true);

                $sheet->setCellValue(sprintf("C%d",$key+7),1);
                $sheet->setCellValue(sprintf("D%d",$key+7),$room->num);
                $sheet->setCellValue(sprintf("E%d",$key+7),$customers);
                $sheet->setCellValue(sprintf("F%d",$key+7),$room->price/$room->num);
               
                $sheet->setCellValue(sprintf("H%d",$key+7),"=".sprintf("D%d",$key+7)."*".sprintf("F%d",$key+7));
                $key++;
            }
        }elseif($order->product->stock == 2){
            $t = json_decode($order->product->rooms, true);
            foreach($order->room as $key=>$room){

                $customers = '';
                $orderRoomCustomer = $room->orderRoomCustomer;
                if($orderRoomCustomer){
                    foreach($orderRoomCustomer as $orc){
                        $name = $orc->orderCustomer->first_name.$orc->orderCustomer->last_name.'、';
                        $customers .= $name;
                    }
                }
              
                $sheet->mergeCells(sprintf("F%d:G%d",$key+7,$key+7));
                $sheet->mergeCells(sprintf("H%d:I%d",$key+7,$key+7));
                $sheet->setCellValue(sprintf("A%d",$key+7),$key+1);
                $sheet->setCellValue(sprintf("B%d",$key+7),$t[$room->room_id]['name']);

                $sheet->getStyle(sprintf("B%d",$key+7))->getAlignment()->setWrapText(true);

                $sheet->setCellValue(sprintf("C%d",$key+7),1);
                $sheet->setCellValue(sprintf("D%d",$key+7),$room->num);
                $sheet->setCellValue(sprintf("E%d",$key+7),$customers);

                $sheet->setCellValue(sprintf("F%d",$key+7),$room->price/$room->num);
               
                $sheet->setCellValue(sprintf("H%d",$key+7),"=".sprintf("D%d",$key+7)."*".sprintf("F%d",$key+7));
                $key++;
            }
        }

        $key2 = 0;
        foreach($order->prices as $op){
            
            $sheet->mergeCells(sprintf("F%d:G%d",$key2+$key+7,$key2+$key+7));
            $sheet->mergeCells(sprintf("H%d:I%d",$key2+$key+7,$key2+$key+7));
            $sheet->setCellValue(sprintf("A%d",$key2+$key+7),$key2+$key+1);
            $sheet->setCellValue(sprintf("B%d",$key2+$key+7),$op->typeObj->name);

            $sheet->getStyle(sprintf("B%d",$key2+$key+7))->getAlignment()->setWrapText(true);

            $sheet->setCellValue(sprintf("C%d",$key2+$key+7),'');
            $sheet->setCellValue(sprintf("D%d",$key2+$key+7),$op->num);
            $sheet->setCellValue(sprintf("E%d",$key2+$key+7),'');
            $sheet->setCellValue(sprintf("F%d",$key2+$key+7),$op->price);
            $sheet->setCellValue(sprintf("H%d",$key2+$key+7),"=".sprintf("D%d",$key2+$key+7)."*".sprintf("F%d",$key2+$key+7));
            $key2++;
        }

        if($order->sale_price != 0){
            $sheet->mergeCells(sprintf("F%d:G%d",$key2+$key+7,$key2+$key+7));
            $sheet->mergeCells(sprintf("H%d:I%d",$key2+$key+7,$key2+$key+7));
            $sheet->setCellValue(sprintf("A%d",$key2+$key+7),$key2+$key+1);
            $sheet->setCellValue(sprintf("B%d",$key2+$key+7),sprintf("优惠金额(%s)",$order->price_desc));

            $sheet->getStyle(sprintf("B%d",$key2+$key+7))->getAlignment()->setWrapText(true);

            $sheet->setCellValue(sprintf("C%d",$key2+$key+7),'');
            $sheet->setCellValue(sprintf("D%d",$key2+$key+7),1);
            $sheet->setCellValue(sprintf("E%d",$key2+$key+7),'');
            $sheet->setCellValue(sprintf("F%d",$key2+$key+7),-($order->sale_price));
            $sheet->setCellValue(sprintf("H%d",$key2+$key+7),"=".sprintf("D%d",$key2+$key+7)."*".sprintf("F%d",$key2+$key+7));
            $key2++;
        }
        
        if($order->commiss != 0){
            $sheet->mergeCells(sprintf("F%d:G%d",$key2+$key+7,$key2+$key+7));
            $sheet->mergeCells(sprintf("H%d:I%d",$key2+$key+7,$key2+$key+7));
            $sheet->setCellValue(sprintf("A%d",$key2+$key+7),$key2+$key+1);
            $sheet->setCellValue(sprintf("B%d",$key2+$key+7),"后返");
            $sheet->setCellValue(sprintf("C%d",$key2+$key+7),'');
            $sheet->setCellValue(sprintf("D%d",$key2+$key+7),1);
            $sheet->setCellValue(sprintf("E%d",$key2+$key+7),'');
            $sheet->setCellValue(sprintf("F%d",$key2+$key+7),-($order->commiss));
            $sheet->setCellValue(sprintf("H%d",$key2+$key+7),"=".sprintf("D%d",$key2+$key+7)."*".sprintf("F%d",$key2+$key+7));
            $key2++;
        }

        if($order->refund_price != 0){
            $sheet->mergeCells(sprintf("F%d:G%d",$key2+$key+7,$key2+$key+7));
            $sheet->mergeCells(sprintf("H%d:I%d",$key2+$key+7,$key2+$key+7));
            $sheet->setCellValue(sprintf("A%d",$key2+$key+7),$key2+$key+1);
            $sheet->setCellValue(sprintf("B%d",$key2+$key+7),"舱房退扣");
            $sheet->setCellValue(sprintf("C%d",$key2+$key+7),'');
            $sheet->setCellValue(sprintf("D%d",$key2+$key+7),1);
            $sheet->setCellValue(sprintf("E%d",$key2+$key+7),'');
            $sheet->setCellValue(sprintf("F%d",$key2+$key+7),($order->refund_price));
            $sheet->setCellValue(sprintf("H%d",$key2+$key+7),"=".sprintf("D%d",$key2+$key+7)."*".sprintf("F%d",$key2+$key+7));
            $key2++;

        }   
       
        $sheet->mergeCells(sprintf("B%d:G%d",$key2+$key+7,$key2+$key+7));
        $sheet->mergeCells(sprintf("H%d:I%d",$key2+$key+7,$key2+$key+7));
        $sheet->setCellValue(sprintf("A%d",$key2+$key+7),"合计");
        $sheet->setCellValue(sprintf("H%d",$key2+$key+7),"=SUM(".sprintf("H%d",7).":".sprintf("H%d",$key2+$key+6).')');

        $sheet->getStyle(sprintf("A2:I%d",$key2+$key+7))->getFont()->setSize(10)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');

        header("Content-Type:text/html;charset=utf-8");
        $wizard = new \PHPExcel_Helper_HTML;
        $html1 = $order->product->cost;
        if(!empty($html1)){
            $html1 = '<font face="微软雅黑" size="10">'.$html1.'.</font>';
            $html1 = $wizard->toRichTextObject($html1);
        }
        
        $html2 = $order->product->nocost;
        if(!empty($html2)){
            $html2 = '<font face="微软雅黑" size="10">'.$html2.'.</font>';
            $html2 = $wizard->toRichTextObject($html2);
        }
        
        $html3 = $order->product->cancel;
        if(!empty($html3)){
            $html3 = '<font face="微软雅黑" size="10">'.$html3.'.</font>';
            $html3 = $wizard->toRichTextObject($html3);
        }
        
        $sheet->getRowDimension($key2+$key+8)->setRowHeight(150);
        $sheet->getRowDimension($key2+$key+9)->setRowHeight(130);
        $sheet->getRowDimension($key2+$key+10)->setRowHeight(100);
        $sheet->getRowDimension($key2+$key+11)->setRowHeight(100);
        $sheet->mergeCells(sprintf("B%d:I%d",$key2+$key+8,$key2+$key+8));
        $sheet->mergeCells(sprintf("B%d:I%d",$key2+$key+9,$key2+$key+9));
        $sheet->mergeCells(sprintf("B%d:I%d",$key2+$key+10,$key2+$key+10));
        $sheet->mergeCells(sprintf("B%d:I%d",$key2+$key+11,$key2+$key+11));
        $sheet->mergeCells(sprintf("A%d:I%d",$key2+$key+12,$key2+$key+12));
        $sheet->setCellValue(sprintf("A%d",$key2+$key+8),"费用包含");
        $sheet->setCellValue(sprintf("A%d",$key2+$key+9),"费用不含");
        $sheet->setCellValue(sprintf("A%d",$key2+$key+10),"预订须知");
        $sheet->setCellValue(sprintf("A%d",$key2+$key+11),"取消政策");
        $sheet->setCellValue(sprintf("A%d",$key2+$key+12),"请贵社细阅读以上约定条款，没有异议的请签字盖章回传我社，谢谢配合！传真件有效！");

        $objRichText = new \PHPExcel_RichText();

        if($order->check == 2){ //优惠金额未审核通过
            $all_price = $order->price - $order->sale_price;
        }else{
            $all_price = $order->price;
        }
         
        $str = sprintf("总计%s于%s之前汇入我社账户",$this->numberTocncap($all_price),date("Y年m月d日",strtotime($end_date)));
        $objRichText->createText("一.");    //写文字
        $objPayable = $objRichText->createTextRun(sprintf("%s",$str));    //写文字
        $objPayable->getFont()->setSize(10)->setName('微软雅黑');
        $objPayable->getFont()->getColor()->setARGB('FFFF0000');
        $objPayable2 = $objRichText->createTextRun("。以确保机位无误并将水单传真至我社！逾期不能保证舱位，敬请谅解！一旦付款确认, 则不能改期、退票、改签、更名；出团前果由贵社自负！请贵社予以理解并确认此项！\n 二.因天气原因等不可抗力因素导致，邮轮公司有权对旅行社的邮轮停靠港口及停靠时间进行调整， 我社保留对邮轮航线进行调整权利！因航线调整，导致的行程更改，散拼价格不变，请客人理解。");
        $objPayable2->getFont()->setSize(10)->setName('微软雅黑');

        $sheet->setCellValue(sprintf("B%d",$key2+$key+10),$objRichText);
        
        $sheet->setCellValue(sprintf("B%d",$key2+$key+8),$html1);
        $sheet->setCellValue(sprintf("B%d",$key2+$key+9),$html2);
        $sheet->setCellValue(sprintf("B%d",$key2+$key+11),$html3);

        $sheet->getStyle(sprintf("B%d:B%d",$key2+$key+8,$key2+$key+11))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
        
        $sheet->getStyle(sprintf("B%d",$key2+$key+8))->getAlignment()->setWrapText(true);
        $sheet->getStyle(sprintf("B%d",$key2+$key+9))->getAlignment()->setWrapText(true);
        $sheet->getStyle(sprintf("B%d",$key2+$key+10))->getAlignment()->setWrapText(true);
        $sheet->getStyle(sprintf("B%d",$key2+$key+11))->getAlignment()->setWrapText(true);

        $sheet->mergeCells(sprintf("A%d:A%d",$key2+$key+13,$key2+$key+14));
        $sheet->mergeCells(sprintf("B%d:D%d",$key2+$key+13,$key2+$key+13));
        $sheet->mergeCells(sprintf("B%d:D%d",$key2+$key+14,$key2+$key+14));
        $sheet->mergeCells(sprintf("E%d:F%d",$key2+$key+13,$key2+$key+13));
        $sheet->mergeCells(sprintf("E%d:F%d",$key2+$key+14,$key2+$key+14));
        $sheet->mergeCells(sprintf("G%d:I%d",$key2+$key+13,$key2+$key+13));
        $sheet->mergeCells(sprintf("G%d:I%d",$key2+$key+14,$key2+$key+14));
        $sheet->setCellValue(sprintf("A%d",$key2+$key+13),"银行信息");
        $sheet->setCellValue(sprintf("B%d",$key2+$key+13),"开户行");
        $sheet->setCellValue(sprintf("E%d",$key2+$key+13),"户名");
        $sheet->setCellValue(sprintf("G%d",$key2+$key+13),"账号");

        if($tem_sheet == 2){
            $sheet->setCellValue(sprintf("B%d",$key2+$key+14),"交通银行北京东直门支行");
            $sheet->setCellValue(sprintf("E%d",$key2+$key+14),"中青旅控股股份有限公司");
            $sheet->setCellValue(sprintf("G%d",$key2+$key+14),"110060635012015127000");
        }else{
            $sheet->setCellValue(sprintf("B%d",$key2+$key+14),"中国农业银行北京先农坛支行");
            $sheet->setCellValue(sprintf("E%d",$key2+$key+14),"易游天下国际旅行社（北京）有限公司");
            $sheet->setCellValue(sprintf("G%d",$key2+$key+14),"11 2001 0104 0008 538");
        }
        
        $sheet->mergeCells(sprintf("A%d:I%d",$key2+$key+15,$key2+$key+15));
        $sheet->setCellValue(sprintf("A%d",$key2+$key+15),sprintf("送客社签字（盖章）：                                                                我社签字（盖章）：%s \n 时间：                                                                                       时间：%s",$order->member->profile->name,date("Y年m月d日")));
        $sheet->getStyle(sprintf("A%d",$key2+$key+15))->getAlignment()->setWrapText(true);
        $sheet->getStyle(sprintf("A%d",$key2+$key+15))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
        $sheet->getRowDimension($key2+$key+15)->setRowHeight(40);

        $sheet->getStyle(sprintf("A2:I%d",$key2+$key+7))->getFont()->setSize(10)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->getStyle(sprintf("A%d:A%d",$key2+$key+7,$key2+$key+14))->getFont()->setSize(10)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->getStyle(sprintf("A%d:I%d",$key2+$key+13,$key2+$key+13))->getFont()->setSize(10)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->getStyle(sprintf("A%d",$key2+$key+12))->getFont()->setSize(10)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('FFFF0000');
        $styleArray = array(  
            'borders' => array(  
                'allborders' => array(  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框   
                    'color' => array('argb' => '000000'),  
                ),  
            ),  
        );
        $sheet->getStyle(sprintf("A1:I%d",$key2+$key+15))->applyFromArray($styleArray);   

        $objDrawing = new \PHPExcel_Worksheet_Drawing();
        if($tem_sheet == 2){
            $objDrawing->setPath("./img/zql.png");
            $objDrawing->setHeight(86);
            $objDrawing->setWidth(200);
        }else{
            $objDrawing->setPath("./img/yiyou.png");
            $objDrawing->setHeight(90);
            $objDrawing->setWidth(90);
        }
        $objDrawing->setOffsetX(1);                       
        $objDrawing->setOffsetY(1); 
        $objDrawing->setCoordinates('A1');
        $objDrawing->setWorksheet($sheet);

        $objDrawing2 = new \PHPExcel_Worksheet_Drawing();
        if($tem_sheet == 2){
            $objDrawing2->setPath("./img/zql2.png");            
            $objDrawing2->setHeight(150.85);
            $objDrawing2->setWidth(165.85);
        }else{
            $objDrawing2->setPath("./img/yiyou2.png");
                               
            $objDrawing2->setHeight(130.85);
            $objDrawing2->setWidth(145.85);
        }
        $objDrawing2->setOffsetX(10); 
        $objDrawing2->setOffsetY(300);
   
        $objDrawing2->setCoordinates(sprintf("G%d",$key2+$key+11));
        $objDrawing2->setWorksheet($sheet);

        
        $pageMargins = $sheet->getPageMargins();

        $margin = 1.78 / 2.54;   
        $marginright = 1 / 2.54;   

        $pageMargins->setTop($margin);       //上边距
        $pageMargins->setBottom($margin); //下
        $pageMargins->setLeft($margin);      //左
        $pageMargins->setRight($marginright);    //右
        $sheet->getPageSetup()->setFitToWidth('1');//自动填充到页面的宽度
        $sheet->getPageSetup()->setFitToHeight('1');//自动填充到页面的高度

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $fileName = sprintf("%s.xls",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        if($tem_sheet == 2){
             $file_name = sprintf("中青旅_订单号：%s.xls",$order->code);
        }else{
             $file_name = sprintf("易旅_订单号：%s.xls",$order->code);
        }
       
        

        header("Content-Type: application/force-download"); 
        header("Content-Type: application/octet-stream"); 
        header("Content-Type: application/download"); 
        header('Content-Disposition:inline;filename="'.$file_name.'"'); 
        header("Content-Transfer-Encoding: binary"); 
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
        header("Pragma: no-cache"); 
        $objWriter->save('php://output'); 
    }

  

     //多个订单确认单
    public function actionNewExcel($id,$product_id,$end_date,$tem_sheet=1){
        if(empty($id) || empty($product_id)){
            return false;
        }
        
        //是否登录
        if(\Yii::$app->user->isGuest){
            echo "尚未登录!";exit();
        }
        

        $ids = explode(',',$id);
        
        $orders = Order::find()->where(['id'=>$ids])->all();
        $product = Product::findOne($product_id);
       

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('确认单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('微软雅黑');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $sheet->getColumnDimension('A')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(35);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(8);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(8);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(9);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(9);// 设置宽度
        $sheet->getRowDimension(1)->setRowHeight(68);
        $sheet->mergeCells('A1:I1');   
        $sheet->mergeCells('B2:I2');
        $sheet->mergeCells('B3:C3');
        $sheet->mergeCells('E3:F3');
        $sheet->mergeCells('H3:I3');
        $sheet->mergeCells('A4:I4');
        $sheet->mergeCells('B5:I5');
        $sheet->mergeCells('F6:G6');
        $sheet->mergeCells('H6:I6');
        if($tem_sheet == 2){
            $sheet->setCellValue("A1","中青旅控股股份有限公司 \n邮轮产品  确认单");
            $sheet->setCellValue("B3","中青旅控股股份有限公司");
        }else{
            $sheet->setCellValue("A1","易游天下国际旅行社（北京）有限公司\n游轮事业部  确认单");
            $sheet->setCellValue("B3","易游天下国际旅行社（北京）有限公司");
        }
       
        $sheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $sheet->getStyle("A1")->getFont()->setSize(16)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
       
        $sheet->setCellValue("A2","TO");
        $sheet->setCellValue("B2","");
        $sheet->setCellValue("A3","From");
        $sheet->setCellValue("D3","销售");
        $sheet->setCellValue("E3",\Yii::$app->user->identity->profile->name);
        $sheet->setCellValue("G3","电话");
        $sheet->setCellValue("H3","");
        $sheet->setCellValue("A4","费用明细");
        $sheet->setCellValue("A5","线路");
        $sheet->setCellValue("B5",date("Y年m月d日",strtotime($product->go_time)).' '.$product->name);
        $sheet->setCellValue("A6","序号");
        $sheet->setCellValue("B6","项目类型");
        $sheet->setCellValue("C6","间数");
        $sheet->setCellValue("D6","人数");
        $sheet->setCellValue("E6","客人姓名");
        $sheet->setCellValue("F6","单价");
        $sheet->setCellValue("H6","合计");
        

        $key = 0;
        $key2 = 0;
        $allPrice = 0; //总金额
        if($orders){
           
            foreach($orders as $orderModel){

                if($orderModel->check == 2){
                    $allPrice += $orderModel->price-$orderModel->sale_price;
                }else{
                    $allPrice += $orderModel->price;
                }
                
               
                if($orderModel->product->stock == 1){
                    foreach($orderModel->room as $room){
                        
                        $customers = '';
                        $orderRoomCustomer = $room->orderRoomCustomer;
                        if($orderRoomCustomer){
                            foreach($orderRoomCustomer as $orc){
                                $name = $orc->orderCustomer->first_name.$orc->orderCustomer->last_name.'、';
                                $customers .= $name;
                            }
                        }

                        $sheet->mergeCells(sprintf("F%d:G%d",$key+7,$key+7));
                        $sheet->mergeCells(sprintf("H%d:I%d",$key+7,$key+7));
                        $sheet->setCellValue(sprintf("A%d",$key+7),$key+1);
                        $sheet->setCellValue(sprintf("B%d",$key+7),$room->productRoom->room->name.$room->productRoom->room->code. $room->productRoom->people.'人间');

                        $sheet->getStyle(sprintf("B%d",$key+7))->getAlignment()->setWrapText(true);

                        $sheet->setCellValue(sprintf("C%d",$key+7),1);
                        $sheet->setCellValue(sprintf("D%d",$key+7),$room->num);
                        $sheet->setCellValue(sprintf("E%d",$key+7),$customers);
                        $sheet->setCellValue(sprintf("F%d",$key+7),$room->price/$room->num); 
                        $sheet->setCellValue(sprintf("H%d",$key+7),"=".sprintf("D%d",$key+7)."*".sprintf("F%d",$key+7));
                        $key++;
                        
                    }
      


                }elseif($orderModel->product->stock == 2){
                    $t = json_decode($orderModel->product->rooms, true);
                    foreach($orderModel->room as $room){
                        $customers = '';
                        $orderRoomCustomer = $room->orderRoomCustomer;
                        if($orderRoomCustomer){
                            foreach($orderRoomCustomer as $orc){
                                $name = $orc->orderCustomer->first_name.$orc->orderCustomer->last_name.'、';
                                $customers .= $name;
                            }
                        }
                        $sheet->mergeCells(sprintf("F%d:G%d",$key+7,$key+7));
                        $sheet->mergeCells(sprintf("H%d:I%d",$key+7,$key+7));
                        $sheet->setCellValue(sprintf("A%d",$key+7),$key+1);
                        $sheet->setCellValue(sprintf("B%d",$key+7),$t[$room->room_id]['name']);

                        $sheet->getStyle(sprintf("B%d",$key+7))->getAlignment()->setWrapText(true);

                        $sheet->setCellValue(sprintf("C%d",$key+7),1);
                        $sheet->setCellValue(sprintf("D%d",$key+7),$room->num);
                        $sheet->setCellValue(sprintf("E%d",$key+7),$customers);
                        $sheet->setCellValue(sprintf("F%d",$key+7),$room->price/$room->num);
                       
                        $sheet->setCellValue(sprintf("H%d",$key+7),"=".sprintf("D%d",$key+7)."*".sprintf("F%d",$key+7));
                        $key++;
                    }
            
                   
                }


                if($orderModel->prices){
                    foreach($orderModel->prices as $op){
                        $sheet->mergeCells(sprintf("F%d:G%d",$key+7,$key+7));
                        $sheet->mergeCells(sprintf("H%d:I%d",$key+7,$key+7));
                        $sheet->setCellValue(sprintf("A%d",$key+7),$key+1);
                        $sheet->setCellValue(sprintf("B%d",$key+7),$op->typeObj->name);

                        $sheet->getStyle(sprintf("B%d",$key+7))->getAlignment()->setWrapText(true);

                        $sheet->setCellValue(sprintf("C%d",$key+7),'');
                        $sheet->setCellValue(sprintf("D%d",$key+7),$op->num);
                        $sheet->setCellValue(sprintf("E%d",$key+7),'');
                        $sheet->setCellValue(sprintf("F%d",$key+7),$op->price);
                        $sheet->setCellValue(sprintf("H%d",$key+7),"=".sprintf("D%d",$key+7)."*".sprintf("F%d",$key+7));
                        $key++;
                    }
                }
                
                if($orderModel->sale_price != 0){ 
                    $sheet->mergeCells(sprintf("F%d:G%d",$key+7,$key+7));
                    $sheet->mergeCells(sprintf("H%d:I%d",$key+7,$key+7));
                    $sheet->setCellValue(sprintf("A%d",$key+7),$key+1);
                    $sheet->setCellValue(sprintf("B%d",$key+7),sprintf("优惠金额(%s)",$orderModel->price_desc));

                    $sheet->getStyle(sprintf("B%d",$key+7))->getAlignment()->setWrapText(true);

                    $sheet->setCellValue(sprintf("C%d",$key+7),'');
                    $sheet->setCellValue(sprintf("D%d",$key+7),1);
                    $sheet->setCellValue(sprintf("E%d",$key+7),'');
                    $sheet->setCellValue(sprintf("F%d",$key+7),-($orderModel->sale_price));
                    $sheet->setCellValue(sprintf("H%d",$key+7),"=".sprintf("D%d",$key+7)."*".sprintf("F%d",$key+7));
                   
                    $key++;
                }

                if($orderModel->commiss != 0){ 
                    $sheet->mergeCells(sprintf("F%d:G%d",$key+7,$key+7));
                    $sheet->mergeCells(sprintf("H%d:I%d",$key+7,$key+7));
                    $sheet->setCellValue(sprintf("A%d",$key+7),$key+1);
                    $sheet->setCellValue(sprintf("B%d",$key+7),"后返");
                    $sheet->setCellValue(sprintf("C%d",$key+7),'');
                    $sheet->setCellValue(sprintf("D%d",$key+7),1);
                    $sheet->setCellValue(sprintf("E%d",$key+7),'');
                    $sheet->setCellValue(sprintf("F%d",$key+7),-($orderModel->commiss));
                    $sheet->setCellValue(sprintf("H%d",$key+7),"=".sprintf("D%d",$key+7)."*".sprintf("F%d",$key+7));
                   
                    $key++;
                }
                if($orderModel->refund_price != 0){ 
                    $sheet->mergeCells(sprintf("F%d:G%d",$key+7,$key+7));
                    $sheet->mergeCells(sprintf("H%d:I%d",$key+7,$key+7));
                    $sheet->setCellValue(sprintf("A%d",$key+7),$key+1);
                    $sheet->setCellValue(sprintf("B%d",$key+7),"舱房退扣");
                    $sheet->setCellValue(sprintf("C%d",$key+7),'');
                    $sheet->setCellValue(sprintf("D%d",$key+7),1);
                    $sheet->setCellValue(sprintf("E%d",$key+7),'');
                    $sheet->setCellValue(sprintf("F%d",$key+7),($orderModel->refund_price));
                    $sheet->setCellValue(sprintf("H%d",$key+7),"=".sprintf("D%d",$key+7)."*".sprintf("F%d",$key+7));

                    $key++;
                }
            }
        }

       
      
        $sheet->mergeCells(sprintf("B%d:G%d",$key+7,$key+7));
        $sheet->mergeCells(sprintf("H%d:I%d",$key+7,$key+7));
        $sheet->setCellValue(sprintf("A%d",$key+7),"合计");
        $sheet->setCellValue(sprintf("H%d",$key+7),"=SUM(".sprintf("H%d",7).":".sprintf("H%d",$key+6).')');

        $sheet->getStyle(sprintf("A2:I%d",$key+7))->getFont()->setSize(10)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');

        header("Content-Type:text/html;charset=utf-8");
        $wizard = new \PHPExcel_Helper_HTML;
        $html1 = !empty($product->cost) ? $product->cost : '-';
        if(!empty($html1)){
            $html1 = '<font face="微软雅黑" size="10">'.$html1.'</font>';
            $html1 = $wizard->toRichTextObject($html1);
        }
        
        $html2 = !empty($product->nocost) ? $product->nocost : '-';
        if(!empty($html2)){
            $html2 = '<font face="微软雅黑" size="10">'.$html2.'</font>';
            $html2 = $wizard->toRichTextObject($html2);
        }
        
        $html3 = !empty($product->cancel) ? $product->cancel : '-';
        if(!empty($html3)){
            $html3 = '<font face="微软雅黑" size="10">'.$html3.'</font>';
            $html3 = $wizard->toRichTextObject($html3);
        }
        
     

        $sheet->getRowDimension($key+8)->setRowHeight(150);
        $sheet->getRowDimension($key+9)->setRowHeight(130);
        $sheet->getRowDimension($key+10)->setRowHeight(100);
        $sheet->getRowDimension($key+11)->setRowHeight(100);
        $sheet->mergeCells(sprintf("B%d:I%d",$key+8,$key+8));
        $sheet->mergeCells(sprintf("B%d:I%d",$key+9,$key+9));
        $sheet->mergeCells(sprintf("B%d:I%d",$key+10,$key+10));
        $sheet->mergeCells(sprintf("B%d:I%d",$key+11,$key+11));
        $sheet->mergeCells(sprintf("A%d:I%d",$key+12,$key+12));
        $sheet->setCellValue(sprintf("A%d",$key+8),"费用包含");
        $sheet->setCellValue(sprintf("A%d",$key+9),"费用不含");
        $sheet->setCellValue(sprintf("A%d",$key+10),"预订须知");
        $sheet->setCellValue(sprintf("A%d",$key+11),"取消政策");
        $sheet->setCellValue(sprintf("A%d",$key+12),"请贵社细阅读以上约定条款，没有异议的请签字盖章回传我社，谢谢配合！传真件有效！");

        $objRichText = new \PHPExcel_RichText();
        
        $str = sprintf("总计%s于%s之前汇入我社账户",$this->numberTocncap((string)sprintf("%.2f",$allPrice)),date("Y年m月d日",strtotime($end_date)));
        $objRichText->createText("一.");    //写文字
        $objPayable = $objRichText->createTextRun(sprintf("%s",$str));    //写文字
        $objPayable->getFont()->setSize(10)->setName('微软雅黑');
        $objPayable->getFont()->getColor()->setARGB('FFFF0000');
        $objPayable2 = $objRichText->createTextRun("。以确保机位无误并将水单传真至我社！逾期不能保证舱位，敬请谅解！一旦付款确认, 则不能改期、退票、改签、更名；出团前果由贵社自负！请贵社予以理解并确认此项！\n 二.因天气原因等不可抗力因素导致，邮轮公司有权对旅行社的邮轮停靠港口及停靠时间进行调整， 我社保留对邮轮航线进行调整权利！因航线调整，导致的行程更改，散拼价格不变，请客人理解。");
        $objPayable2->getFont()->setSize(10)->setName('微软雅黑');

       
        $sheet->setCellValue(sprintf("B%d",$key+10),$objRichText);
       
        
        $sheet->setCellValue(sprintf("B%d",$key+8),$html1);
        $sheet->setCellValue(sprintf("B%d",$key+9),$html2);
        $sheet->setCellValue(sprintf("B%d",$key+11),$html3);

        $sheet->getStyle(sprintf("B%d:B%d",$key+8,$key+11))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
        
        $sheet->getStyle(sprintf("B%d",$key+8))->getAlignment()->setWrapText(true);
        $sheet->getStyle(sprintf("B%d",$key+9))->getAlignment()->setWrapText(true);
        $sheet->getStyle(sprintf("B%d",$key+10))->getAlignment()->setWrapText(true);
        $sheet->getStyle(sprintf("B%d",$key+11))->getAlignment()->setWrapText(true);

        $sheet->mergeCells(sprintf("A%d:A%d",$key+13,$key+14));
        $sheet->mergeCells(sprintf("B%d:D%d",$key+13,$key+13));
        $sheet->mergeCells(sprintf("B%d:D%d",$key+14,$key+14));
        $sheet->mergeCells(sprintf("E%d:F%d",$key+13,$key+13));
        $sheet->mergeCells(sprintf("E%d:F%d",$key+14,$key+14));
        $sheet->mergeCells(sprintf("G%d:I%d",$key+13,$key+13));
        $sheet->mergeCells(sprintf("G%d:I%d",$key+14,$key+14));
        $sheet->setCellValue(sprintf("A%d",$key+13),"银行信息");
       
        $sheet->setCellValue(sprintf("B%d",$key+13),"开户行");
        $sheet->setCellValue(sprintf("E%d",$key+13),"户名");
        $sheet->setCellValue(sprintf("G%d",$key+13),"账号");
        
        if($tem_sheet == 2){
            $sheet->setCellValue(sprintf("B%d",$key+14),"交通银行北京东直门支行");
            $sheet->setCellValue(sprintf("E%d",$key+14),"中青旅控股股份有限公司");
            $sheet->setCellValue(sprintf("G%d",$key+14),"110060635012015127000");
        }else{
            $sheet->setCellValue(sprintf("B%d",$key+14),"中国农业银行北京先农坛支行");
            $sheet->setCellValue(sprintf("E%d",$key+14),"易游天下国际旅行社（北京）有限公司");
            $sheet->setCellValue(sprintf("G%d",$key+14),"11 2001 0104 0008 538");
        }

        
        $sheet->mergeCells(sprintf("A%d:I%d",$key+15,$key+15));
        $sheet->setCellValue(sprintf("A%d",$key+15),sprintf("送客社签字（盖章）：                                                                我社签字（盖章）：%s \n 时间：                                                                                       时间：%s",\Yii::$app->user->identity->profile->name,date("Y年m月d日")));
        $sheet->getStyle(sprintf("A%d",$key+15))->getAlignment()->setWrapText(true);
        $sheet->getStyle(sprintf("A%d",$key+15))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
        $sheet->getRowDimension($key+15)->setRowHeight(40);

        $sheet->getStyle(sprintf("A2:I%d",$key+7))->getFont()->setSize(10)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->getStyle(sprintf("A%d:A%d",$key+7,$key+14))->getFont()->setSize(10)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->getStyle(sprintf("A%d:I%d",$key+13,$key+13))->getFont()->setSize(10)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('000000');
        $sheet->getStyle(sprintf("A%d",$key+12))->getFont()->setSize(10)->setBold(true)->setName('微软雅黑')->getColor()->setARGB('FFFF0000');
        $styleArray = array(  
            'borders' => array(  
                'allborders' => array(  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框   
                    'color' => array('argb' => '000000'),  
                ),  
            ),  
        );
        $sheet->getStyle(sprintf("A1:I%d",$key+15))->applyFromArray($styleArray);   

        $objDrawing = new \PHPExcel_Worksheet_Drawing();
        if($tem_sheet == 2){
            $objDrawing->setPath("./img/zql.png");
            $objDrawing->setHeight(86);
            $objDrawing->setWidth(200);
        }else{
            $objDrawing->setPath("./img/yiyou.png");
            $objDrawing->setHeight(90);
            $objDrawing->setWidth(90);
        }
        $objDrawing->setOffsetX(1);                       
        $objDrawing->setOffsetY(1); 
        $objDrawing->setCoordinates('A1');
        $objDrawing->setWorksheet($sheet);

        $objDrawing2 = new \PHPExcel_Worksheet_Drawing();
        if($tem_sheet == 2){
            $objDrawing2->setPath("./img/zql2.png");            
            $objDrawing2->setHeight(150.85);
            $objDrawing2->setWidth(165.85);
        }else{
            $objDrawing2->setPath("./img/yiyou2.png");                  
            $objDrawing2->setHeight(130.85);
            $objDrawing2->setWidth(145.85);
        }
        $objDrawing2->setOffsetX(10); 
        $objDrawing2->setOffsetY(300);                       
        $objDrawing2->setCoordinates(sprintf("G%d",$key+11));
        $objDrawing2->setWorksheet($sheet);

        
        $pageMargins = $sheet->getPageMargins();

        $margin = 1.78 / 2.54;   
        $marginright = 1 / 2.54;   

        $pageMargins->setTop($margin);       //上边距
        $pageMargins->setBottom($margin); //下
        $pageMargins->setLeft($margin);      //左
        $pageMargins->setRight($marginright);    //右
        $sheet->getPageSetup()->setFitToWidth('1');//自动填充到页面的宽度
        $sheet->getPageSetup()->setFitToHeight('1');//自动填充到页面的高度

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $fileName = sprintf("%s.xls",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        if($tem_sheet == 2){
            $file_name = sprintf("中青旅_订单：%s.xls",$product->code);
        }else{
            $file_name = sprintf("易旅_订单：%s.xls",$product->code);
        }
        
        header("Content-type: application/octet-stream");    
        header("Accept-Ranges: bytes");    
        header("Accept-Length: " . filesize($file));    
        header("Content-Disposition: attachment; filename=" .$file_name);    
        @readfile($file);

        // header("Content-Type: application/force-download"); 
        // header("Content-Type: application/octet-stream"); 
        // header("Content-Type: application/download"); 
        // header('Content-Disposition:inline;filename="'.$file_name.'"'); 
        // header("Content-Transfer-Encoding: binary"); 
        // header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 
        // header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
        // header("Pragma: no-cache"); 
        // $objWriter->save('php://output'); 
    }

    //金额转大写
    private function numberTocncap($number = 0, $int_unit = '元', $is_round = TRUE, $is_extra_zero = FALSE) 
    { 
        // 将数字切分成两段 
        $parts = explode('.', $number, 2); 
        $int = isset($parts[0]) ? strval($parts[0]) : '0'; 
        $dec = isset($parts[1]) ? strval($parts[1]) : ''; 
     
        // 如果小数点后多于2位，不四舍五入就直接截，否则就处理 
        $dec_len = strlen($dec); 
        if (isset($parts[1]) && $dec_len > 2) 
        { 
            $dec = $is_round 
                    ? substr(strrchr(strval(round(floatval("0.".$dec), 2)), '.'), 1) 
                    : substr($parts[1], 0, 2); 
        } 
     
        // 当number为0.001时，小数点后的金额为0元 
        if(empty($int) && empty($dec)) 
        { 
            return '零'; 
        } 
     
        // 定义 
        $chs = array('0','壹','贰','叁','肆','伍','陆','柒','捌','玖'); 
        $uni = array('','拾','佰','仟'); 
        $dec_uni = array('角', '分'); 
        $exp = array('', '万'); 
        $res = ''; 
     
        // 整数部分从右向左找 
        for($i = strlen($int) - 1, $k = 0; $i >= 0; $k++) 
        { 
            $str = ''; 
            // 按照中文读写习惯，每4个字为一段进行转化，i一直在减 
            for($j = 0; $j < 4 && $i >= 0; $j++, $i--) 
            { 
                $u = $int{$i} > 0 ? $uni[$j] : ''; // 非0的数字后面添加单位 
                $str = $chs[$int{$i}] . $u . $str; 
            } 
            //echo $str."|".($k - 2)."<br>"; 
            $str = rtrim($str, '0');// 去掉末尾的0 
            $str = preg_replace("/0+/", "零", $str); // 替换多个连续的0 
            if(!isset($exp[$k])) 
            { 
                $exp[$k] = $exp[$k - 2] . '亿'; // 构建单位 
            } 
            $u2 = $str != '' ? $exp[$k] : ''; 
            $res = $str . $u2 . $res; 
        } 
     
        // 如果小数部分处理完之后是00，需要处理下 
        $dec = rtrim($dec, '0'); 
     
        // 小数部分从左向右找 
        if(!empty($dec)) 
        { 
            $res .= $int_unit; 
     
            // 是否要在整数部分以0结尾的数字后附加0，有的系统有这要求 
            if ($is_extra_zero) 
            { 
                if (substr($int, -1) === '0') 
                { 
                    $res.= '零'; 
                } 
            } 
     
            for($i = 0, $cnt = strlen($dec); $i < $cnt; $i++) 
            { 
                $u = $dec{$i} > 0 ? $dec_uni[$i] : ''; // 非0的数字后面添加单位 
                $res .= $chs[$dec{$i}] . $u; 
            } 
            $res = rtrim($res, '0');// 去掉末尾的0 
            $res = preg_replace("/0+/", "零", $res); // 替换多个连续的0 
        } 
        else 
        { 
            $res .= $int_unit . '整'; 
        } 
        return $res; 
    } 
    private function numberTocncapllllx($data){
       $capnum = array( "零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖" );
       $capdigit = array( "", "拾", "佰", "仟" );
       $subdata = explode( ".", $data );
       $yuan = $subdata[0];
       $j = 0;
       $nonzero = 0;
       for( $i=0; $i<strlen($subdata[0]); $i++ ){
         if( $i==0 ){ //确定个位
          if($subdata[1]){
            $cncap = (substr($subdata[0],-1,1)!=0) ? "元" : "元零";
          }else{
            $cncap = "元";
          }
         }
         if( $i==4 ){ //确定万位
            $j = 0;
            $nonzero = 0;
            $cncap = "万" . $cncap;
         }
         if($i==8){ //确定亿位
            $j = 0;
            $nonzero = 0;
            $cncap = "亿" . $cncap;
         }
         $numb = substr($yuan,-1,1); //截取尾数
         $cncap = ($numb) ? $capnum[$numb].$capdigit[$j].$cncap : (($nonzero)?"零".$cncap:$cncap);
         $nonzero = ($numb) ? 1 : $nonzero;
         $yuan = substr($yuan,0,strlen($yuan)-1); //截去尾数   
         $j++;
       }  
       if($subdata[1]){
        $chiao=(substr($subdata[1],0,1))?$capnum[substr($subdata[1],0,1)]."角":"零";
        $cent=(substr($subdata[1],1,1))?$capnum[substr($subdata[1],1,1)]."分":"";
       }
       $cncap .= $chiao.$cent."整";
       $cncap = preg_replace("/(零)+/","\\1",$cncap); //合并连续“零”
       return $cncap;
    }

    private function htmlTostring($str){
        $str = preg_replace("/<style .*?<\/style>/is", "", $str);  $str = preg_replace("/<script .*?<\/script>/is", "", $str);  
        $str = preg_replace("/<br \s*\/?\/>/i", "\n", $str);  
        $str = preg_replace("/<\/?p>/i", "", $str);  
        $str = preg_replace("/<\/?td>/i", "", $str);  
        $str = preg_replace("/<\/?div>/i", "", $str);  
        $str = preg_replace("/<\/?blockquote>/i", "", $str);  
        //$str = preg_replace("/<\/?li>/i", "\n", $str);
        $str = preg_replace("/<\/li>/i", "", $str); 
        $str = preg_replace("/<li>/i", "*)", $str);
       
        $str = preg_replace("/\&nbsp\;/i", " ", $str);  
        $str = preg_replace("/\&nbsp/i", " ", $str);  
        $str = preg_replace("/\&amp\;/i", "&", $str);  
        $str = preg_replace("/\&amp/i", "&", $str);    
        $str = preg_replace("/\&lt\;/i", "<", $str);  
        $str = preg_replace("/\&lt/i", "<", $str);  
        $str = preg_replace("/\&ldquo\;/i", '"', $str);  
        $str = preg_replace("/\&ldquo/i", '"', $str);  
        $str = preg_replace("/\&lsquo\;/i", "'", $str);  
        $str = preg_replace("/\&lsquo/i", "'", $str);  
        $str = preg_replace("/\&rsquo\;/i", "'", $str);  
        $str = preg_replace("/\&rsquo/i", "'", $str);  
        $str = preg_replace("/\&gt\;/i", ">", $str);   
        $str = preg_replace("/\&gt/i", ">", $str);   
        $str = preg_replace("/\&rdquo\;/i", '"', $str);   
        $str = preg_replace("/\&rdquo/i", '"', $str);   
        $str = strip_tags($str);  
        //$str = html_entity_decode($str, ENT_QUOTES, $encode);  
        $str = preg_replace("/\&\#.*?\;/i", "", $str);          

        return $str;
    }

    //下载销售的的excel
    public function actionSellExcel($product_id){
        $product = Product::findOne($product_id);
        if(empty($product)){
            return false;
        }
        //是否登录
        if(\Yii::$app->user->isGuest){
            echo "尚未登录!";exit();
        }
        
        //获取所有订单
        if(\yii::$app->user->identity->id == 1){
            $all_orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else{
            $all_orders = Order::find()->where(['product_id'=>$product_id,'member_id'=>\Yii::$app->user->identity->id])->andWhere(['<=','status',4])->all();
        }
        
        if(!empty($all_orders)){
            //所有房型
            $room_types = [];
            $room_arr = [];
            $i = 0;
            foreach($all_orders as $order){
                foreach($order->room as $room){
                    if($order->product->stock == 1){ //标准
                        $room_name = $room->productRoom->room->name.$room->productRoom->room->code.$room->num.'人';
                    }else if($order->product->stock == 2){
                        $t = json_decode($order->product->rooms, true);    
                        $room_name = $t[$room->room_id]['name'];
                    }
                    if($room->stock == 1){
                        if(in_array($room_name,$room_types)){
                            $key = array_search($room_name,$room_types); 
                            if($order->status == 4){
                                $room_arr[$key]['s1'] += 1;
                            }else if($order->status == 3){
                                $room_arr[$key]['s2'] += 1;
                            }else if($order->status == 1){
                                $room_arr[$key]['s3'] += 1;
                            }
                        }else{
                            $room_arr[$i] = ['name'=>$room_name,'s1'=>($order->status == 4)?1:0,'s2'=>($order->status == 3)?1:0,'s3'=>($order->status == 1)?1:0];
                            $room_types[$i] = $room_name;
                            $i++;
                        }
                    }
                        
                }
            }

            $price_types = [];
            $price_arr = [];
            $p = 0;
            foreach($all_orders as $order){
                foreach($order->prices as $price){
                    $price_name = $price->type;
                    if(in_array($price_name,$price_types)){
                        $key1 = array_search($price_name,$price_types);
                        $num1 = count(json_decode($price->customers)); 
                        if($order->status == 4){
                            $price_arr[$key1]['s1'] += $num1;
                        }else if($order->status == 3){
                            $price_arr[$key1]['s2'] += $num1;
                        }else if($order->status == 1){
                            $price_arr[$key1]['s3'] += $num1;
                        }
                    }else{
                        $num1 = count(json_decode($price->customers)); 
                        $price_arr[$p] = ['name'=>$price->typeObj->name,'s1'=>($order->status == 4)?$num1:0,'s2'=>($order->status == 3)?$num1:0,'s3'=>($order->status == 1)?$num1:0];
                        $price_types[$p] = $price_name;
                        $p++;
                    }
                }    
            }
        }

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('统计');
        $sheet->getDefaultStyle()->getFont()->setSize(9)->setName('微软雅黑');
        $sheet->getDefaultStyle()->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $styleArray = array(  
            'borders' => array(  
                'allborders' => array(  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框   
                    'color' => array('argb' => '000000'),  
                ),  
            ),  
        );   

        $sheet->getColumnDimension('A')->setWidth(30);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(20);// 设置宽度

        $array_1 = ['房型','已提交','已付款','占位','合计'];
        $sheet->fromArray($array_1, NULL, 'A2');

        $k1 = 0;
        if(!empty($room_arr)){
            foreach($room_arr as $val){
                $sheet->setCellValue(sprintf("A%d",$k1+3),$val['name']);
                $sheet->setCellValue(sprintf("B%d",$k1+3),$val['s1']);
                $sheet->setCellValue(sprintf("C%d",$k1+3),$val['s2']);
                $sheet->setCellValue(sprintf("D%d",$k1+3),$val['s3']);
                $sheet->setCellValue(sprintf("E%d",$k1+3),$val['s1']+$val['s2']+$val['s3']);
                $k1++;
            } 
        }

        $sheet->getStyle(sprintf("A2:E2"))->getFont()->setBold(true);
        $sheet->getStyle(sprintf("A2:E%d",$k1+2))->applyFromArray($styleArray);

        $k2 = $k1+4;
        $array_2 = ['附加费','已提交','已付款','占位','合计'];
        $sheet->fromArray($array_2, NULL,sprintf("A%d",$k2));

        if(!empty($price_arr)){
            foreach($price_arr as $val){
                $sheet->setCellValue(sprintf("A%d",$k2+1),$val['name']);
                $sheet->setCellValue(sprintf("B%d",$k2+1),$val['s1']);
                $sheet->setCellValue(sprintf("C%d",$k2+1),$val['s2']);
                $sheet->setCellValue(sprintf("D%d",$k2+1),$val['s3']);
                $sheet->setCellValue(sprintf("E%d",$k2+1),$val['s1']+$val['s2']+$val['s3']);
                $k2++;
            } 
        }

        $sheet->getStyle(sprintf("A%d:E%d",$k1+4,$k1+4))->getFont()->setBold(true);
        $sheet->getStyle(sprintf("A%d:E%d",$k1+4,$k2))->applyFromArray($styleArray);

        $k3 = $k2+2; 
        $array_3 = ['订单号码','订单金额','订单状态'];
        $sheet->fromArray($array_3, NULL,sprintf("A%d",$k3));
        if(!empty($all_orders)){
            foreach($all_orders as $val){
                $sheet->setCellValueExplicit(sprintf("A%d",$k3+1),$val->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet->setCellValue(sprintf("B%d",$k3+1),$val->price);
                $sheet->setCellValue(sprintf("C%d",$k3+1),\yii::$app->params['order_status'][$val->status]);
                $k3++;
            }
        }
        $sheet->setCellValue(sprintf("A%d",$k3+1),'合计');
        $sheet->setCellValue(sprintf("B%d",$k3+1),"=SUM(".sprintf("B%d",$k2+3).":".sprintf("B%d",$k3).')');

        $sheet->getStyle(sprintf("A%d:E%d",$k2+2,$k2+2))->getFont()->setBold(true);
        $sheet->getStyle(sprintf("A%d:C%d",$k2+2,$k3+1))->applyFromArray($styleArray);

        $objWorkSheet = $objPHPExcel->createSheet(1);
        $objWorkSheet->setTitle('明细');
        $objWorkSheet->getDefaultStyle()->getFont()->setSize(9)->setName('微软雅黑');
        $objWorkSheet->getDefaultStyle()->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);

        $objWorkSheet->getRowDimension(1)->setRowHeight(30);

        $array_4 = ['销售负责人','客户订单号','关联订单','下单日期','付款日期','订单总结算价','订单人数' ,'房间总数', '联系人公司','联系人','联系人电话','巴士总人数','房型','房型结算价','客人中文名','客人英文名','护照号', '出游人号码','类型','备注'];
        //该航线下所有附加费
        $priceType = $product->priceType;
        $type_arr = [];
        if(!empty($priceType)){
            foreach($priceType as $type){
                $type_arr[$type->id] = $type->name;
            }
            $objWorkSheet->fromArray($type_arr, NULL, 'U1');
        }

        $objWorkSheet->fromArray($array_4, NULL, 'A1');

        $abc = ['A','B','C','D','E','F','G','H','I','J','K','L'];
        foreach($abc as $a){
            $objWorkSheet->getColumnDimension($a)->setWidth(20);// 设置宽度
        }
        $objWorkSheet->getColumnDimension('M')->setWidth(24);
        $abc2 = ['O','P','Q','R','S','T'];
        foreach($abc2 as $a){
            $objWorkSheet->getColumnDimension($a)->setWidth(20);// 设置宽度
        }
        $kroom = 0;
        $korder = 0;
        $priceType_con = [];
        if(!empty($all_orders)){
            foreach($all_orders as $order){
                //获取已付款时间
                $pay_time = '';
                if($order->status >= 3){
                    $logs = Logs::find()->where(['code'=>$order->id,'type'=>1])->andWhere(['like', 'desc', '已付款'])->orderBy('id asc')->one();
                    if($logs){
                        $pay_time = date('Y-m-d',strtotime($logs->created_at));
                    }else{
                        $pay_time = date('Y-m-d',strtotime($order->created_at));
                    }
                }

                //获取附加费
                $price_type_arr = [];
                if(!empty($order->priceAll)){
                    foreach($order->priceAll as $priceval){
                        $price_type_arr[$priceval->type] = json_decode($priceval->customers,true);
                    }
                }
                //大巴人数
                $car_num = 0;
                //获取所有房型
                foreach($order->room as $room){
                    if($order->product->stock == 1){ 
                        $room_name = $room->productRoom->room->name.$room->productRoom->room->code.' '.$room->productRoom->people.'人间';
                    }else if($order->product->stock == 2){
                        $t = json_decode($order->product->rooms, true);
                        $room_name = $t[$room->room_id]['name'];
                    }
                    if($room->stock == 1){
                        $objWorkSheet->setCellValue(sprintf("M%d",$kroom+2),$room_name);
                        $cus_num = $room->orderRoomCustomerNum;
                        if($room->merge_id){ //拼房房间的人
                            $merge_room = $room->mergeRoom;
                            $cus_num = $cus_num + $merge_room->orderRoomCustomerNum;
                        }
                        if($cus_num > 0){
                            $objWorkSheet->mergeCells(sprintf("M%d:M%d",$kroom+2,$kroom+1+$cus_num)); 
                        }
                        if( $cus_num > 0){
                            foreach($room->orderRoomCustomer as $customer){
                                $objWorkSheet->setCellValue(sprintf("N%d",$kroom+2),$room->price*$room->num);
                                $objWorkSheet->setCellValue(sprintf("O%d",$kroom+2),
                                    $customer->orderCustomer->first_name.$customer->orderCustomer->last_name);
                                $objWorkSheet->setCellValue(sprintf("P%d",$kroom+2),
                                    $customer->orderCustomer->en_first_name.$customer->orderCustomer->en_last_name);
                                $objWorkSheet->setCellValue(sprintf("Q%d",$kroom+2),
                                    $customer->orderCustomer->passport);
                                $objWorkSheet->setCellValue(sprintf("R%d",$kroom+2),
                                    $customer->orderCustomer->phone);
                                if(!empty($customer->orderCustomer->birthday)){
                                    $b_type = ($this->birthday($customer->orderCustomer->birthday) > 18)?'成人':'儿童';
                                }else{
                                    $b_type = '';
                                }
                                $objWorkSheet->setCellValue(sprintf("S%d",$kroom+2),
                                    $b_type);
                             
                                if(!empty($type_arr)){
                                    foreach($type_arr as $tykey=>$tyval){
                                         if(isset($price_type_arr[$tykey]) && in_array($customer->customer_id, $price_type_arr[$tykey])){

                                            if(strpos($tyval,'大巴') !== false){
                                                $car_num++;
                                            }
                                            $priceType_con[$kroom][$tykey]='参加'; 
                                         }else{
                                             $priceType_con[$kroom][$tykey]='';
                                         }
                                    }
                                }  
                                $kroom++; 
                            }
                            if($room->merge_id){ //拼房房间的人
                                $merge_room = $room->mergeRoom;
                                $customer_merge = $merge_room->orderRoomCustomer;
                                //获取拼住附加费
                                $merge_price_type_arr = [];
                                if(!empty($merge_room->order->priceAll)){
                                    foreach($merge_room->order->priceAll as $priceval){
                                        $merge_price_type_arr[$priceval->type] = json_decode($priceval->customers,true);
                                    }
                                }
                                if($customer_merge){
                                    foreach($customer_merge as $customer){
                                       $objWorkSheet->setCellValue(sprintf("N%d",$kroom+2),$room->price*$room->num);
                                       $objWorkSheet->setCellValue(sprintf("O%d",$kroom+2),$customer->orderCustomer->first_name.$customer->orderCustomer->last_name.'*');
                                       $objWorkSheet->setCellValue(sprintf("P%d",$kroom+2),$customer->orderCustomer->en_first_name.$customer->orderCustomer->en_last_name);
                                       $objWorkSheet->setCellValue(sprintf("Q%d",$kroom+2),$customer->orderCustomer->passport);
                                       $objWorkSheet->setCellValue(sprintf("R%d",$kroom+2),$customer->orderCustomer->phone);
                                       if(!empty($customer->orderCustomer->birthday)){
                                           $b_type = ($this->birthday($customer->orderCustomer->birthday) > 18)?'成人':'儿童';
                                        }else{
                                            $b_type = '';
                                        }
                                        $objWorkSheet->setCellValue(sprintf("S%d",$kroom+2),$b_type);
                                        if(!empty($type_arr)){
                                            foreach($type_arr as $tykey=>$tyval){
                                             if(isset($merge_price_type_arr[$tykey]) && in_array($customer->customer_id, $merge_price_type_arr[$tykey])){
                                                if(strpos($tyval,'大巴') !== false){
                                                    $car_num++;
                                                }
                                                $priceType_con[$kroom][$tykey]='参加'; 
                                             }else{
                                                $priceType_con[$kroom][$tykey]='';
                                                }
                                            } 
                                        } 
                                        $kroom++;
                                    }
                                }
                            }
                            $kroom--;
                        }else{
                            $priceType_con[$kroom][]=[];
                        }
                        $kroom++;
                    }
                    
                }

                if($order->roomNum == 0){
                    $kroom ++;
                } 
                
                //获取该订单下所有关联单
                $relation = $order->allRelation;
                $str_rel = '';
                foreach($relation as $r){
                    $str_rel .= $r->code.';';
                };
                
                foreach($abc as $a){
                    $objWorkSheet->mergeCells(sprintf("%s%d:%s%d",$a,$korder+2,$a,$kroom+1));   
                }
                $objWorkSheet->mergeCells(sprintf("T%d:T%d",$korder+2,$kroom+1)); 
                $objWorkSheet->setCellValue(sprintf("A%d",$korder+2),$order->member->profile->name);
                $objWorkSheet->setCellValueExplicit(sprintf("B%d",$korder+2),$order->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                $objWorkSheet->setCellValue(sprintf("C%d",$korder+2),$str_rel);
                $objWorkSheet->getStyle(sprintf("C%d",$korder+2))->getAlignment()->setWrapText(true);
                $objWorkSheet->setCellValue(sprintf("D%d",$korder+2),date("Y-m-d",strtotime($order->created_at)));
                $objWorkSheet->setCellValue(sprintf("E%d",$korder+2),$pay_time);
                $objWorkSheet->setCellValue(sprintf("F%d",$korder+2),$order->price);
                $objWorkSheet->setCellValue(sprintf("G%d",$korder+2),$order->customerCount);
                $objWorkSheet->setCellValue(sprintf("H%d",$korder+2),$order->roomNum);
                $objWorkSheet->setCellValue(sprintf("I%d",$korder+2),$order->company);
                $objWorkSheet->setCellValue(sprintf("J%d",$korder+2),$order->username);
                $objWorkSheet->setCellValue(sprintf("K%d",$korder+2),$order->phone);
                $objWorkSheet->setCellValue(sprintf("L%d",$korder+2),$car_num);
                $objWorkSheet->setCellValue(sprintf("T%d",$korder+2),$order->remark);
                $korder = $kroom;

                
            }
        }
        
        $objWorkSheet->fromArray($priceType_con, NULL, 'U2');
        
        $num_abc_arr = [0=>'T',1=>'U',2=>'V',3=>'W',4=>'X',5=>'Y',6=>'Z',7=>'AA',8=>'AB',9=>'AC',10=>'AD',11=>'AE',12=>'AF',13=>'AG',14=>'AH',15=>'AI',16=>'AJ',17=>'AK',18=>'AL',19=>'AM',20=>'AN',21=>'AO',22=>'AP',23=>'AQ',24=>'AR',25=>'AS',26=>'AT',27=>'AU'];
        $num_abc = count($type_arr);
        if(empty($num_abc)){
            $num_abc = 0;
        }

        $objWorkSheet->getStyle(sprintf("A1:%s1",$num_abc_arr[$num_abc]))->getFont()->setBold(true);

        
        $objWorkSheet->getStyle(sprintf("A1:%s%d",$num_abc_arr[$num_abc],$kroom+1))->applyFromArray($styleArray);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $file_name = sprintf("%s%s.xlsx",date("Y年m月d日",strtotime($product->go_time)),$product->cruise->name);

        header("Content-Type: application/force-download"); 
        header("Content-Type: application/octet-stream"); 
        header("Content-Type: application/download"); 
        header('Content-Disposition:inline;filename="'.$file_name.'"'); 
        header("Content-Transfer-Encoding: binary"); 
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
        header("Pragma: no-cache"); 
        $objWriter->save('php://output'); 



    }


    private function birthday($birthday){
        $age = strtotime($birthday); 
        if($age === false){ 
           return false; 
        } 
        list($y1,$m1,$d1) = explode("-",date("Y-m-d",$age)); 
        $now = strtotime("now"); 
        list($y2,$m2,$d2) = explode("-",date("Y-m-d",$now)); 
        $age = $y2 - $y1; 
        if((int)($m2.$d2) < (int)($m1.$d1)) 
          $age -= 1; 
        return $age; 
    }


    //边检信息报备表 excel
    public function actionReportExcel($product_id){
        $product = Product::findOne($product_id);
        if(empty($product)){
            return false;
        }
        //是否登录
        if(\Yii::$app->user->isGuest){
            echo "尚未登录!";exit();
        }
        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('报备表');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setBold(true)->setName('宋体')->getColor()->setARGB("FF0000");
        $sheet->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(14);
        $sheet->getRowDimension(1)->setRowHeight(30); 
        $sheet->getRowDimension(2)->setRowHeight(20); 

        $sheet->getColumnDimension('A')->setWidth(6);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(28);// 设置宽度

        $sheet->mergeCells("A1:J1");

        $sheet->setCellValue('A1',sprintf("%s %s航次游客信息报备新增项目",$product->cruise->name,$product->code));

        $titleArray = ['护照号','姓名','性别','出生日期','出生地','护照签发地','身份证号','身份证签发地','民族'];
        $sheet->fromArray($titleArray, NULL, 'B2');
        $sheet->getStyle('A1:J1')->getFont()->setSize(14)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);
        $sheet->getStyle("A1:J1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A2:J2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);
        $sheet->getStyle("A2:J2")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);


        $all_orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();

        $key = 0 ;
        if(!empty($all_orders)){
            foreach($all_orders as $v){
                //先找房间 再找人
                foreach($v->room as $room){
                    if($room->stock == 1){ //计入库存
                        foreach($room->orderRoomCustomer as $val){  
                            if($val->orderCustomer->country == '中国'){
                                $sheet->setCellValue(sprintf("A%d",$key+3),$key+1);
                                $sheet->setCellValue(sprintf("B%d",$key+3),$val->orderCustomer->passport);
                                $sheet->setCellValue(sprintf("C%d",$key+3),$val->orderCustomer->first_name.$val->orderCustomer->last_name);
                                $sheet->setCellValue(sprintf("D%d",$key+3),yii::$app->params['gender'][$val->orderCustomer->gender]);
                                $sheet->setCellValue(sprintf("E%d",$key+3),($val->orderCustomer->birthday)?date("Y年m月d日",strtotime($val->orderCustomer->birthday)):'');
                                $sheet->setCellValue(sprintf("F%d",$key+3),$val->orderCustomer->native);
                                $sheet->setCellValue(sprintf("G%d",$key+3),$val->orderCustomer->passport_add);
                                $sheet->setCellValueExplicit(sprintf("H%d",$key+3),$val->orderCustomer->card,\PHPExcel_Cell_DataType::TYPE_STRING);
                                $sheet->setCellValue(sprintf("I%d",$key+3),$val->orderCustomer->card_address);
                                $sheet->setCellValue(sprintf("J%d",$key+3),$val->orderCustomer->nation);
                                $sheet->getRowDimension($key+3)->setRowHeight(20);
                                // $sheet->getStyle(sprintf("A%d:J%d",$key+3,$key+3))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFFF00');
                                $key++;
                            } 
                           
                        }       

                        if($room->merge_id){ //拼房房间的人
                            $merge_room = $room->mergeRoom;     
                            $customers = $merge_room->orderRoomCustomer;
                            if($customers){
                                foreach($customers as $val){   
                                    if($val->orderCustomer->country == '中国'){
                                        $sheet->setCellValue(sprintf("A%d",$key+3),$key+1);
                                        $sheet->setCellValue(sprintf("B%d",$key+3),$val->orderCustomer->passport);
                                        $sheet->setCellValue(sprintf("C%d",$key+3),$val->orderCustomer->first_name.$val->orderCustomer->last_name);
                                        $sheet->setCellValue(sprintf("D%d",$key+3),yii::$app->params['gender'][$val->orderCustomer->gender]);
                                        $sheet->setCellValue(sprintf("E%d",$key+3),($val->orderCustomer->birthday)?date("Y年m月d日",strtotime($val->orderCustomer->birthday)):'');
                                        $sheet->setCellValue(sprintf("F%d",$key+3),$val->orderCustomer->native);
                                        $sheet->setCellValue(sprintf("G%d",$key+3),$val->orderCustomer->passport_add);
                                        $sheet->setCellValue(sprintf("H%d",$key+3),$val->orderCustomer->card);
                                        $sheet->setCellValueExplicit(sprintf("H%d",$key+3),$val->orderCustomer->card,\PHPExcel_Cell_DataType::TYPE_STRING);
                                        $sheet->setCellValue(sprintf("J%d",$key+3),$val->orderCustomer->nation);

                                        $sheet->getRowDimension($key+3)->setRowHeight(20);
                                        // $sheet->getStyle(sprintf("A%d:J%d",$key+3,$key+3))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFFF00');
                                        $key++;
                                    } 
                                }     
                            }
                        }
                    }
                }
            }

        }
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle(sprintf("A2:J%d",$key+2))->applyFromArray($styleArray);
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $file_name = sprintf("%s%s.xlsx",date("Y年m月d日",strtotime($product->go_time)),$product->cruise->name);

        header("Content-Type: application/force-download"); 
        header("Content-Type: application/octet-stream"); 
        header("Content-Type: application/download"); 
        header('Content-Disposition:inline;filename="'.$file_name.'"'); 
        header("Content-Transfer-Encoding: binary"); 
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
        header("Pragma: no-cache"); 
        $objWriter->save('php://output'); 

    }

    //订单列表根据条件 导出列表
    public function actionOrderExcel($show=1){
        $searchModel = new OrderSearch();
        $data = Yii::$app->request->queryParams;
        // if(isset($data['OrderSearch']['product_id'])){
        //     $product_id = $data['OrderSearch']['product_id'];
        // }else{
        //     $product_id = NULL;
        // }

        $orders = $searchModel->excel_search(Yii::$app->request->queryParams,$show);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('订单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('Arial');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(20);

        $array_title = ['团号','订单号','关联单','公司名称','联系人','预订日期','销售','应收' ,'已收','欠款金额','应收款审批金额','护照信息','身份证信息','原件还是复印件','是否开票','房型','房间数','入住人数','大巴数','房号','分团','附加费信息','旅客姓名', '游客联系方式'];
        $sheet->fromArray($array_title, NULL, 'A1');
        $sheet->getStyle('A1:R1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);

        //$abc = ['A','B','C','D','E','F','G','H','I','J','K','O'];
        $abc = ['A','B','C','F','G','H','I','J','K','O'];
        $kroom = 0;
        $korder = 0;
        if(!empty($orders)){
            foreach($orders as $order){
                
                $priceAll = $order->priceAll; //该订单下所有附加费

                foreach($order->room as $room){
                    if($order->product->stock == 1){ 
                        $room_name = $room->productRoom->room->name.$room->productRoom->room->code.' '.$room->productRoom->people.'人间';
                    }else if($order->product->stock == 2){
                        $t = json_decode($order->product->rooms, true);
                        $room_name = $t[$room->room_id]['name'];
                    } 
                    if($room->stock == 1){
                        //获取游客姓名
                        $customer_name = '';
                        //附加费
                        $price_type = '';
                        //联系人
                        $contact = '';

                        //大巴人数 
                        $car_num = 0;
                        //护照信息
                        $pass_msg = '齐';
                        //身份证信息
                        $card_msg = '齐';
                        //原件还是复印件
                        $pass_type = '原件';

                        $customers = $room->orderRoomCustomer;

                        if($customers){
                            $bool = true;
                            foreach($customers as $customer){
                                $customer_name .= $customer->orderCustomer->first_name.$customer->orderCustomer->last_name.';';
                                if(!$customer->orderCustomer->passport || !$customer->orderCustomer->passport_add || !$customer->orderCustomer->passport_grant || !$customer->orderCustomer->passport_date || !$customer->orderCustomer->passport_img){
                                    $pass_msg = '不齐';
                                }
                                if(!$customer->orderCustomer->card || !$customer->orderCustomer->card_address || !$customer->orderCustomer->card_img){
                                    $card_msg = '不齐';
                                }
                                if($customer->orderCustomer->passport_type == 2){
                                    $pass_type = '复印件';
                                }
                                if($priceAll){
                                    foreach($priceAll as $price){
                                         if(in_array($customer->orderCustomer->id,json_decode($price->customers,true))){
                                             $price_type .= $price->typeObj->name.'+';
                                            if(strpos($price->typeObj->name,'大巴') !== false){
                                                $car_num++;
                                            }
                                         } 
                                    } 
                                    $price_type .= ';'; 
                                }
                                if($customer->orderCustomer->phone && $bool){
                                    $bool = false;
                                    $contact .= $customer->orderCustomer->first_name.$customer->orderCustomer->last_name.' '.$customer->orderCustomer->phone;
                                }
                            }
                        }else{
                            $pass_msg = '没有录入人';
                            $card_msg = '没有录入人';
                            $pass_type = '没有录入人';
                        }
                        if($room->merge_id){ //拼房房间的人
                            $merge_room = $room->mergeRoom;
                            //拼房 附加费
                            $merge_customers = $merge_room->orderRoomCustomer;
                            if($merge_customers){
                                foreach($merge_customers as $mer_cus){
                                    $customer_name .= $mer_cus->orderCustomer->first_name.$mer_cus->orderCustomer->last_name.';';
                                    if(!$mer_cus->orderCustomer->passport || !$mer_cus->orderCustomer->passport_add || !$mer_cus->orderCustomer->passport_grant || !$mer_cus->orderCustomer->passport_date || !$mer_cus->orderCustomer->passport_img){
                                        $pass_msg = '不齐';
                                    }
                                    if(!$mer_cus->orderCustomer->card || !$mer_cus->orderCustomer->card_address || !$mer_cus->orderCustomer->card_img){
                                        $card_msg = '不齐';
                                    }
                                    if($mer_cus->orderCustomer->passport_type == 2){
                                        $pass_type = '复印件';
                                    }
                                    if($merge_room->order->priceAll){
                                        foreach($merge_room->order->priceAll as $price){
                                             if(in_array($mer_cus->orderCustomer->id,json_decode($price->customers,true))){
                                                 $price_type .= $price->typeObj->name.'+';
                                                 if(strpos($price->typeObj->name,'大巴') !== false){
                                                    $car_num++;
                                                 }
                                             } 
                                        } 
                                        $price_type .= ';'; 
                                    }
                                }
                            }
                        }

                        $sheet->setCellValue(sprintf("D%d",$kroom+2),$order->company);
                        $sheet->setCellValue(sprintf("E%d",$kroom+2),$order->username);
                        
                        $sheet->setCellValue(sprintf("L%d",$kroom+2),$pass_msg);
                        $sheet->setCellValue(sprintf("M%d",$kroom+2),$card_msg);
                        $sheet->setCellValue(sprintf("N%d",$kroom+2),$pass_type);
                        $sheet->setCellValue(sprintf("P%d",$kroom+2),$room_name);
                        $sheet->setCellValue(sprintf("Q%d",$kroom+2),(int)1);
                        $sheet->setCellValue(sprintf("R%d",$kroom+2),$room->num);
                        $sheet->setCellValue(sprintf("S%d",$kroom+2),$car_num);
                        $sheet->setCellValue(sprintf("T%d",$kroom+2),$room->code);
                        $sheet->setCellValue(sprintf("U%d",$kroom+2),($order->assign)? $order->assign->name : '');
                        $sheet->setCellValue(sprintf("V%d",$kroom+2),$price_type);
                        $sheet->getStyle(sprintf("V%d",$kroom+2))->getAlignment()->setWrapText(true);
                        $sheet->setCellValue(sprintf("W%d",$kroom+2),$customer_name);
                        $sheet->getStyle(sprintf("W%d",$kroom+2))->getAlignment()->setWrapText(true);
                        $sheet->setCellValue(sprintf("X%d",$kroom+2),$contact);
                        $sheet->getStyle(sprintf("X%d",$kroom+2))->getAlignment()->setWrapText(true);
                        $kroom++;
                    }else if($room->stock == 2){
                        //获取游客姓名
                        $customer_name = '';
                        //附加费
                        $price_type = '';
                        //联系人
                        $contact = '';

                        //大巴人数 
                        $car_num = 0;
                        //护照信息
                        $pass_msg = '齐';
                        //身份证信息
                        $card_msg = '齐';
                        //原件还是复印件
                        $pass_type = '原件';

                        $customers = $room->orderRoomCustomer;

                        if($customers){
                            $bool = true;
                            foreach($customers as $customer){
                                $customer_name .= $customer->orderCustomer->first_name.$customer->orderCustomer->last_name.';';
                                if(!$customer->orderCustomer->passport || !$customer->orderCustomer->passport_add || !$customer->orderCustomer->passport_grant || !$customer->orderCustomer->passport_date || !$customer->orderCustomer->passport_img){
                                    $pass_msg = '不齐';
                                }
                                if(!$customer->orderCustomer->card || !$customer->orderCustomer->card_address || !$customer->orderCustomer->card_img){
                                    $card_msg = '不齐';
                                }
                                if($customer->orderCustomer->passport_type == 2){
                                    $pass_type = '复印件';
                                }
                                if($priceAll){
                                    foreach($priceAll as $price){
                                         if(in_array($customer->orderCustomer->id,json_decode($price->customers,true))){
                                             $price_type .= $price->typeObj->name.'+';
                                            if(strpos($price->typeObj->name,'大巴') !== false){
                                                $car_num++;
                                            }
                                         } 
                                    } 
                                    $price_type .= ';'; 
                                }
                                if($customer->orderCustomer->phone && $bool){
                                    $bool = false;
                                    $contact .= $customer->orderCustomer->first_name.$customer->orderCustomer->last_name.' '.$customer->orderCustomer->phone;
                                }
                            }
                        }else{
                            $pass_msg = '没有录入人';
                            $card_msg = '没有录入人';
                            $pass_type = '没有录入人';
                        }
                        if($room->merge_id){ //拼房房间的人
                            $merge_room = $room->mergeRoom;
                            //拼房 附加费
                            $merge_customers = $merge_room->orderRoomCustomer;
                            if($merge_customers){
                                foreach($merge_customers as $mer_cus){
                                    $customer_name .= $mer_cus->orderCustomer->first_name.$mer_cus->orderCustomer->last_name.';';
                                    if(!$mer_cus->orderCustomer->passport || !$mer_cus->orderCustomer->passport_add || !$mer_cus->orderCustomer->passport_grant || !$mer_cus->orderCustomer->passport_date || !$mer_cus->orderCustomer->passport_img){
                                        $pass_msg = '不齐';
                                    }
                                    if(!$mer_cus->orderCustomer->card || !$mer_cus->orderCustomer->card_address || !$mer_cus->orderCustomer->card_img){
                                        $card_msg = '不齐';
                                    }
                                    if($mer_cus->orderCustomer->passport_type == 2){
                                        $pass_type = '复印件';
                                    }
                                    if($merge_room->order->priceAll){
                                        foreach($merge_room->order->priceAll as $price){
                                             if(in_array($mer_cus->orderCustomer->id,json_decode($price->customers,true))){
                                                 $price_type .= $price->typeObj->name.'+';
                                                 if(strpos($price->typeObj->name,'大巴') !== false){
                                                    $car_num++;
                                                 }
                                             } 
                                        } 
                                        $price_type .= ';'; 
                                    }
                                }
                            }
                        }
                        $contact .= '(拼房)';
                        $sheet->getStyle(sprintf("L%d:X%d",$kroom+2,$kroom+2))->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FF0000');

                        $sheet->setCellValue(sprintf("D%d",$kroom+2),$order->company);
                        $sheet->setCellValue(sprintf("E%d",$kroom+2),$order->username);

                        $sheet->setCellValue(sprintf("L%d",$kroom+2),$pass_msg);
                        $sheet->setCellValue(sprintf("M%d",$kroom+2),$card_msg);
                        $sheet->setCellValue(sprintf("N%d",$kroom+2),$pass_type);
                        $sheet->setCellValue(sprintf("P%d",$kroom+2),$room_name);
                        $sheet->setCellValue(sprintf("Q%d",$kroom+2),0);
                        $sheet->setCellValue(sprintf("R%d",$kroom+2),$room->num);
                        $sheet->setCellValue(sprintf("S%d",$kroom+2),0);
                        $sheet->setCellValue(sprintf("T%d",$kroom+2),$room->code);
                        $sheet->setCellValue(sprintf("U%d",$kroom+2),($order->assign)? $order->assign->name : '');
                        $sheet->setCellValue(sprintf("V%d",$kroom+2),$price_type);
                        $sheet->getStyle(sprintf("V%d",$kroom+2))->getAlignment()->setWrapText(true);
                        $sheet->setCellValue(sprintf("W%d",$kroom+2),$customer_name);
                        $sheet->getStyle(sprintf("W%d",$kroom+2))->getAlignment()->setWrapText(true);
                        $sheet->setCellValue(sprintf("X%d",$kroom+2),$contact);
                        $sheet->getStyle(sprintf("X%d",$kroom+2))->getAlignment()->setWrapText(true);
                    }
                    
                }

                if($order->roomNum == 0){
                    $kroom ++;
                } 
            
                //获取该订单下所有关联单
                $relation = $order->allRelation;
                $str_rel = '';
                foreach($relation as $r){
                    $str_rel .= $r->code.';';
                };
            
                foreach($abc as $a){
                    $sheet->mergeCells(sprintf("%s%d:%s%d",$a,$korder+2,$a,$kroom+1));   
                } 
                $sheet->setCellValue(sprintf("A%d",$korder+2),$order->product->code);
                $sheet->setCellValueExplicit(sprintf("B%d",$korder+2),$order->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet->setCellValue(sprintf("C%d",$korder+2),$str_rel);
                $sheet->getStyle(sprintf("C%d",$korder+2))->getAlignment()->setWrapText(true);
                //$sheet->setCellValue(sprintf("D%d",$korder+2),$order->company);
                //$sheet->setCellValue(sprintf("E%d",$korder+2),$order->username);
                $sheet->setCellValue(sprintf("F%d",$korder+2),date("Y-m-d H:i:s",strtotime($order->created_at)));
                $sheet->setCellValue(sprintf("G%d",$korder+2),$order->member->profile->name);

                if($order->check == 2){ //优惠金额未审核通过
                    $all_price = $order->price - $order->sale_price;
                }else{
                    $all_price = $order->price;
                }

                $sheet->setCellValue(sprintf("H%d",$korder+2),$all_price);
                $sheet->setCellValue(sprintf("I%d",$korder+2),$order->financeTotalPrice);
                $sheet->setCellValue(sprintf("J%d",$korder+2),$all_price-$order->financeTotalPrice);
                $sheet->setCellValue(sprintf("K%d",$korder+2),$order->financeMustPrice);
                $sheet->setCellValue(sprintf("O%d",$korder+2),'');
                $korder = $kroom;

            }
        }

        $sheet->setCellValue(sprintf("H%d",($korder>1)?$korder+2:$korder+2), sprintf("=SUM(H2:H%d)",($korder>1)?$korder+1:$korder+2));
        $sheet->setCellValue(sprintf("I%d",($korder>1)?$korder+2:$korder+2), sprintf("=SUM(I2:I%d)",($korder>1)?$korder+1:$korder+2));
        $sheet->setCellValue(sprintf("J%d",($korder>1)?$korder+2:$korder+2), sprintf("=SUM(J2:J%d)",($korder>1)?$korder+1:$korder+2));
        $sheet->setCellValue(sprintf("K%d",($korder>1)?$korder+2:$korder+2), sprintf("=SUM(K2:K%d)",($korder>1)?$korder+1:$korder+2));
        $sheet->setCellValue(sprintf("Q%d",($korder>1)?$korder+2:$korder+2),sprintf("=SUM(Q2:Q%d)",($korder>1)?$korder+1:$korder+2));
        $sheet->setCellValue(sprintf("S%d",($korder>1)?$korder+2:$korder+2),sprintf("=SUM(S2:S%d)",($korder>1)?$korder+1:$korder+2));
            
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle(sprintf("A2:X%d",($korder>0)?$korder+1:$korder+2))->applyFromArray($styleArray);        

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $file_name = sprintf("%s.xlsx",'订单信息表');
        header("Content-Type: application/force-download"); 
        header("Content-Type: application/octet-stream"); 
        header("Content-Type: application/download"); 
        header('Content-Disposition:inline;filename="'.$file_name.'"'); 
        header("Content-Transfer-Encoding: binary"); 
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
        header("Pragma: no-cache"); 
        $objWriter->save('php://output'); 
        

    }

    //销售船期 导出列表
    public function actionProductExcel($product_id){
        $product = Product::findOne($product_id);
        if(empty($product)){
            return false;
        }
        //是否登录
        if(\Yii::$app->user->isGuest){
            echo "尚未登录!";exit();
        }
        
        //获取所有订单
        if(Yii::$app->user->can('order_all')){
            $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else{
            $orders = Order::find()->where(['product_id'=>$product_id,'member_id'=>\Yii::$app->user->identity->id])->andWhere(['<=','status',4])->all();
        }
        

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('船期订单列表');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('Arial');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(20);

        $array_title = ['团号','订单号','关联单','公司名称','联系人','预订日期','销售','应收' ,'已收','欠款金额','应收款审批金额','护照信息','身份证信息','原件还是复印件','是否开票','房型','房间数','入住人数','大巴数','房号','分团','附加费信息','旅客姓名', '游客联系方式'];
        $sheet->fromArray($array_title, NULL, 'A1');
        $sheet->getStyle('A1:R1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);

        //$abc = ['A','B','C','D','E','F','G','H','I','J','K','O'];
        $abc = ['A','B','C','F','G','H','I','J','K','O'];
        $kroom = 0;
        $korder = 0;
        if(!empty($orders)){
            foreach($orders as $order){
                
                $priceAll = $order->priceAll; //该订单下所有附加费

                foreach($order->room as $room){
                    if($order->product->stock == 1){ 
                        $room_name = $room->productRoom->room->name.$room->productRoom->room->code.' '.$room->productRoom->people.'人间';
                    }else if($order->product->stock == 2){
                        $t = json_decode($order->product->rooms, true);
                        $room_name = $t[$room->room_id]['name'];
                    } 
                    if($room->stock == 1){
                        //获取游客姓名
                        $customer_name = '';
                        //附加费
                        $price_type = '';
                        //联系人
                        $contact = '';

                        //大巴人数 
                        $car_num = 0;
                        //护照信息
                        $pass_msg = '齐';
                        //身份证信息
                        $card_msg = '齐';
                        //原件还是复印件
                        $pass_type = '原件';

                        $customers = $room->orderRoomCustomer;

                        if($customers){
                            $bool = true;
                            foreach($customers as $customer){
                                $customer_name .= $customer->orderCustomer->first_name.$customer->orderCustomer->last_name.';';
                                if(!$customer->orderCustomer->passport || !$customer->orderCustomer->passport_add || !$customer->orderCustomer->passport_grant || !$customer->orderCustomer->passport_date || !$customer->orderCustomer->passport_img){
                                    $pass_msg = '不齐';
                                }
                                if(!$customer->orderCustomer->card || !$customer->orderCustomer->card_address || !$customer->orderCustomer->card_img){
                                    $card_msg = '不齐';
                                }
                                if($customer->orderCustomer->passport_type == 2){
                                    $pass_type = '复印件';
                                }
                                if($priceAll){
                                    foreach($priceAll as $price){
                                         if(in_array($customer->orderCustomer->id,json_decode($price->customers,true))){
                                             $price_type .= $price->typeObj->name.'+';
                                            if(strpos($price->typeObj->name,'大巴') !== false){
                                                $car_num++;
                                            }
                                         } 
                                    } 
                                    $price_type .= ';'; 
                                }
                                if($customer->orderCustomer->phone && $bool){
                                    $bool = false;
                                    $contact .= $customer->orderCustomer->first_name.$customer->orderCustomer->last_name.' '.$customer->orderCustomer->phone;
                                }
                            }
                        }else{
                            $pass_msg = '没有录入人';
                            $card_msg = '没有录入人';
                            $pass_type = '没有录入人';
                        }
                        if($room->merge_id){ //拼房房间的人
                            $merge_room = $room->mergeRoom;
                            //拼房 附加费
                            $merge_customers = $merge_room->orderRoomCustomer;
                            if($merge_customers){
                                foreach($merge_customers as $mer_cus){
                                    $customer_name .= $mer_cus->orderCustomer->first_name.$mer_cus->orderCustomer->last_name.';';
                                    if(!$mer_cus->orderCustomer->passport || !$mer_cus->orderCustomer->passport_add || !$mer_cus->orderCustomer->passport_grant || !$mer_cus->orderCustomer->passport_date || !$mer_cus->orderCustomer->passport_img){
                                        $pass_msg = '不齐';
                                    }
                                    if(!$mer_cus->orderCustomer->card || !$mer_cus->orderCustomer->card_address || !$mer_cus->orderCustomer->card_img){
                                        $card_msg = '不齐';
                                    }
                                    if($mer_cus->orderCustomer->passport_type == 2){
                                        $pass_type = '复印件';
                                    }
                                    if($merge_room->order->priceAll){
                                        foreach($merge_room->order->priceAll as $price){
                                             if(in_array($mer_cus->orderCustomer->id,json_decode($price->customers,true))){
                                                 $price_type .= $price->typeObj->name.'+';
                                                 if(strpos($price->typeObj->name,'大巴') !== false){
                                                    $car_num++;
                                                 }
                                             } 
                                        } 
                                        $price_type .= ';'; 
                                    }
                                }
                            }
                        }

                        $sheet->setCellValue(sprintf("D%d",$kroom+2),$order->company);
                        $sheet->setCellValue(sprintf("E%d",$kroom+2),$order->username);

                        $sheet->setCellValue(sprintf("L%d",$kroom+2),$pass_msg);
                        $sheet->setCellValue(sprintf("M%d",$kroom+2),$card_msg);
                        $sheet->setCellValue(sprintf("N%d",$kroom+2),$pass_type);
                        $sheet->setCellValue(sprintf("P%d",$kroom+2),$room_name);
                        $sheet->setCellValue(sprintf("Q%d",$kroom+2),(int)1);
                        $sheet->setCellValue(sprintf("R%d",$kroom+2),$room->num);
                        $sheet->setCellValue(sprintf("S%d",$kroom+2),$car_num);
                        $sheet->setCellValue(sprintf("T%d",$kroom+2),$room->code);
                        $sheet->setCellValue(sprintf("U%d",$kroom+2),($order->assign)? $order->assign->name : '');
                        $sheet->setCellValue(sprintf("V%d",$kroom+2),$price_type);
                        $sheet->getStyle(sprintf("V%d",$kroom+2))->getAlignment()->setWrapText(true);
                        $sheet->setCellValue(sprintf("W%d",$kroom+2),$customer_name);
                        $sheet->getStyle(sprintf("W%d",$kroom+2))->getAlignment()->setWrapText(true);
                        $sheet->setCellValue(sprintf("X%d",$kroom+2),$contact);
                        $sheet->getStyle(sprintf("X%d",$kroom+2))->getAlignment()->setWrapText(true);
                        $kroom++;
                    }else if($room->stock == 2){
                        //获取游客姓名
                        $customer_name = '';
                        //附加费
                        $price_type = '';
                        //联系人
                        $contact = '';

                        //大巴人数 
                        $car_num = 0;
                        //护照信息
                        $pass_msg = '齐';
                        //身份证信息
                        $card_msg = '齐';
                        //原件还是复印件
                        $pass_type = '原件';

                        $customers = $room->orderRoomCustomer;

                        if($customers){
                            $bool = true;
                            foreach($customers as $customer){
                                $customer_name .= $customer->orderCustomer->first_name.$customer->orderCustomer->last_name.';';
                                if(!$customer->orderCustomer->passport || !$customer->orderCustomer->passport_add || !$customer->orderCustomer->passport_grant || !$customer->orderCustomer->passport_date || !$customer->orderCustomer->passport_img){
                                    $pass_msg = '不齐';
                                }
                                if(!$customer->orderCustomer->card || !$customer->orderCustomer->card_address || !$customer->orderCustomer->card_img){
                                    $card_msg = '不齐';
                                }
                                if($customer->orderCustomer->passport_type == 2){
                                    $pass_type = '复印件';
                                }
                                if($priceAll){
                                    foreach($priceAll as $price){
                                         if(in_array($customer->orderCustomer->id,json_decode($price->customers,true))){
                                             $price_type .= $price->typeObj->name.'+';
                                            if(strpos($price->typeObj->name,'大巴') !== false){
                                                $car_num++;
                                            }
                                         } 
                                    } 
                                    $price_type .= ';'; 
                                }
                                if($customer->orderCustomer->phone && $bool){
                                    $bool = false;
                                    $contact .= $customer->orderCustomer->first_name.$customer->orderCustomer->last_name.' '.$customer->orderCustomer->phone;
                                }
                            }
                        }else{
                            $pass_msg = '没有录入人';
                            $card_msg = '没有录入人';
                            $pass_type = '没有录入人';
                        }
                        if($room->merge_id){ //拼房房间的人
                            $merge_room = $room->mergeRoom;
                            //拼房 附加费
                            $merge_customers = $merge_room->orderRoomCustomer;
                            if($merge_customers){
                                foreach($merge_customers as $mer_cus){
                                    $customer_name .= $mer_cus->orderCustomer->first_name.$mer_cus->orderCustomer->last_name.';';
                                    if(!$mer_cus->orderCustomer->passport || !$mer_cus->orderCustomer->passport_add || !$mer_cus->orderCustomer->passport_grant || !$mer_cus->orderCustomer->passport_date || !$mer_cus->orderCustomer->passport_img){
                                        $pass_msg = '不齐';
                                    }
                                    if(!$mer_cus->orderCustomer->card || !$mer_cus->orderCustomer->card_address || !$mer_cus->orderCustomer->card_img){
                                        $card_msg = '不齐';
                                    }
                                    if($mer_cus->orderCustomer->passport_type == 2){
                                        $pass_type = '复印件';
                                    }
                                    if($merge_room->order->priceAll){
                                        foreach($merge_room->order->priceAll as $price){
                                             if(in_array($mer_cus->orderCustomer->id,json_decode($price->customers,true))){
                                                 $price_type .= $price->typeObj->name.'+';
                                                 if(strpos($price->typeObj->name,'大巴') !== false){
                                                    $car_num++;
                                                 }
                                             } 
                                        } 
                                        $price_type .= ';'; 
                                    }
                                }
                            }
                        }
                        $contact .= '(拼房)';
                        $sheet->getStyle(sprintf("L%d:X%d",$kroom+2,$kroom+2))->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FF0000');

                        $sheet->setCellValue(sprintf("D%d",$kroom+2),$order->company);
                        $sheet->setCellValue(sprintf("E%d",$kroom+2),$order->username);

                        $sheet->setCellValue(sprintf("L%d",$kroom+2),$pass_msg);
                        $sheet->setCellValue(sprintf("M%d",$kroom+2),$card_msg);
                        $sheet->setCellValue(sprintf("N%d",$kroom+2),$pass_type);
                        $sheet->setCellValue(sprintf("P%d",$kroom+2),$room_name);
                        $sheet->setCellValue(sprintf("Q%d",$kroom+2),0);
                        $sheet->setCellValue(sprintf("R%d",$kroom+2),$room->num);
                        $sheet->setCellValue(sprintf("S%d",$kroom+2),0);
                        $sheet->setCellValue(sprintf("T%d",$kroom+2),$room->code);
                        $sheet->setCellValue(sprintf("U%d",$kroom+2),($order->assign)? $order->assign->name : '');
                        $sheet->setCellValue(sprintf("V%d",$kroom+2),$price_type);
                        $sheet->getStyle(sprintf("V%d",$kroom+2))->getAlignment()->setWrapText(true);
                        $sheet->setCellValue(sprintf("W%d",$kroom+2),$customer_name);
                        $sheet->getStyle(sprintf("W%d",$kroom+2))->getAlignment()->setWrapText(true);
                        $sheet->setCellValue(sprintf("X%d",$kroom+2),$contact);
                        $sheet->getStyle(sprintf("X%d",$kroom+2))->getAlignment()->setWrapText(true);
                    }
                    
                }

                if($order->roomNum == 0){
                    $kroom ++;
                } 
            
                //获取该订单下所有关联单
                $relation = $order->allRelation;
                $str_rel = '';
                foreach($relation as $r){
                    $str_rel .= $r->code.';';
                };
            
                foreach($abc as $a){
                    $sheet->mergeCells(sprintf("%s%d:%s%d",$a,$korder+2,$a,$kroom+1));   
                } 
                $sheet->setCellValue(sprintf("A%d",$korder+2),$order->product->code);
                $sheet->setCellValueExplicit(sprintf("B%d",$korder+2),$order->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet->setCellValue(sprintf("C%d",$korder+2),$str_rel);
                $sheet->getStyle(sprintf("C%d",$korder+2))->getAlignment()->setWrapText(true);
                //$sheet->setCellValue(sprintf("D%d",$korder+2),$order->company);
                //$sheet->setCellValue(sprintf("E%d",$korder+2),$order->username);
                $sheet->setCellValue(sprintf("F%d",$korder+2),date("Y-m-d H:i:s",strtotime($order->created_at)));
                $sheet->setCellValue(sprintf("G%d",$korder+2),$order->member->profile->name);

                if($order->check == 2){ //优惠金额未审核通过
                    $all_price = $order->price - $order->sale_price;
                }else{
                    $all_price = $order->price;
                }

                $sheet->setCellValue(sprintf("H%d",$korder+2),$all_price);
                $sheet->setCellValue(sprintf("I%d",$korder+2),$order->financeTotalPrice);
                $sheet->setCellValue(sprintf("J%d",$korder+2),$all_price-$order->financeTotalPrice);
                $sheet->setCellValue(sprintf("K%d",$korder+2),$order->financeMustPrice);
                $sheet->setCellValue(sprintf("O%d",$korder+2),'');
                $korder = $kroom;

            }
        }

        $sheet->setCellValue(sprintf("H%d",($korder>1)?$korder+2:$korder+2), sprintf("=SUM(H2:H%d)",($korder>1)?$korder+1:$korder+2));
        $sheet->setCellValue(sprintf("I%d",($korder>1)?$korder+2:$korder+2), sprintf("=SUM(I2:I%d)",($korder>1)?$korder+1:$korder+2));
        $sheet->setCellValue(sprintf("J%d",($korder>1)?$korder+2:$korder+2), sprintf("=SUM(J2:J%d)",($korder>1)?$korder+1:$korder+2));
        $sheet->setCellValue(sprintf("K%d",($korder>1)?$korder+2:$korder+2), sprintf("=SUM(K2:K%d)",($korder>1)?$korder+1:$korder+2));
        $sheet->setCellValue(sprintf("Q%d",($korder>1)?$korder+2:$korder+2),sprintf("=SUM(Q2:Q%d)",($korder>1)?$korder+1:$korder+2));
        $sheet->setCellValue(sprintf("S%d",($korder>1)?$korder+2:$korder+2),sprintf("=SUM(S2:S%d)",($korder>1)?$korder+1:$korder+2));
            
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle(sprintf("A2:X%d",($korder>0)?$korder+1:$korder+2))->applyFromArray($styleArray);        

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $file_name = sprintf("%s.xlsx",'订单信息表');
        header("Content-Type: application/force-download"); 
        header("Content-Type: application/octet-stream"); 
        header("Content-Type: application/download"); 
        header('Content-Disposition:inline;filename="'.$file_name.'"'); 
        header("Content-Transfer-Encoding: binary"); 
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
        header("Pragma: no-cache"); 
        $objWriter->save('php://output'); 
        

    }

    //财务单导出 
    public function actionFinanceExcel(){
        $searchModel = new FinanceSearch();
        $all = $searchModel->search_excel(Yii::$app->request->queryParams);

        $objPHPExcel = new \PHPExcel();

        $workSheet = $objPHPExcel->getActiveSheet(0);
        $workSheet->setTitle('汇总');
        $workSheet->getDefaultStyle()->getFont()->setSize(11)->setName('宋体');
        $workSheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $workSheet->getDefaultColumnDimension()->setWidth(15);
        $workSheet->getColumnDimension('A')->setWidth(5);// 设置宽度
        $array_title_1 = ['团号','订单号','应收金额','实收金额','欠款','优惠金额','销售','订单状态'];
        $workSheet->fromArray($array_title_1, NULL, 'B2');

        if($all){
            $fid_arr = [];
            foreach($all as $val){
                $fid_arr[] = $val->id;
            }

            $query = new Query;
            $orders = $query->select(['id','order_id','sum(price) total','order_code','product_id','product_code'])->from('finance_order')->where(['in','finance_id',$fid_arr])->groupBy('order_id')->all();

            if($orders){
                $key_1 = 0;
                foreach($orders as $or){
                    $order = Order::findOne($or['order_id']);
                    $workSheet->setCellValue(sprintf("B%d",$key_1+3),$or['product_code']);
                    $workSheet->setCellValueExplicit(sprintf("C%d",$key_1+3),$or['order_code'],\PHPExcel_Cell_DataType::TYPE_STRING);
                    $workSheet->setCellValue(sprintf("D%d",$key_1+3),$order->price);
                    $workSheet->setCellValue(sprintf("E%d",$key_1+3),$or['total']);
                    $workSheet->setCellValue(sprintf("F%d",$key_1+3),$order->price-$or['total']);
                    $workSheet->setCellValue(sprintf("G%d",$key_1+3),$order->sale_price);
                    $workSheet->setCellValue(sprintf("H%d",$key_1+3),$order->member->profile->name);
                    $workSheet->setCellValue(sprintf("I%d",$key_1+3),\yii::$app->params['order_status'][$order->status]);

                    $key_1++;
                        
                }

            }
           
          
        }


        $sheet = $objPHPExcel->createSheet(1);
        $sheet->setTitle('财务');
        $sheet->getDefaultStyle()->getFont()->setSize(11)->setName('宋体');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(15);
        $sheet->getColumnDimension('A')->setWidth(5);// 设置宽度
        $array_title = ['团号','订单号','应收金额','实收金额','优惠金额','销售','付款单位' ,'付款账号', '付款时间','描述'];
        $sheet->fromArray($array_title, NULL, 'B2');

        if($all){
            $key = 0;
            foreach($all as $f){
                if($f->order){       
                    foreach($f->order as $fd){
                        $pay_time = '';
                        if($fd->order->status >= 3){
                            $logs = Logs::find()->where(['code'=>$fd->order->id,'type'=>1])->andWhere(['like', 'desc', '已付款'])->orderBy('id asc')->one();
                            if($logs){
                                $pay_time = date('Y-m-d',strtotime($logs->created_at));
                            }else{
                                $pay_time = date('Y-m-d',strtotime($fd->order->created_at));
                            }
                        }
                        $sheet->setCellValue(sprintf("B%d",$key+3),$fd->order->product->code);
                        $sheet->setCellValueExplicit(sprintf("C%d",$key+3),$fd->order->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                        $sheet->setCellValue(sprintf("D%d",$key+3),$fd->order->price);
                        $sheet->setCellValue(sprintf("E%d",$key+3),$fd->price);
                        $sheet->setCellValue(sprintf("F%d",$key+3),$fd->order->sale_price);
                        $sheet->setCellValue(sprintf("G%d",$key+3),$fd->order->member->profile->name);
                        $sheet->setCellValue(sprintf("H%d",$key+3),$f->company);
                        $sheet->setCellValueExplicit(sprintf("I%d",$key+3),$f->account,\PHPExcel_Cell_DataType::TYPE_STRING);
                        $sheet->setCellValue(sprintf("J%d",$key+3),$pay_time);
                        $sheet->setCellValue(sprintf("K%d",$key+3),$f->desc);

                        $key++;
                        
                    }
                } 
            }
          
        }

        $styleArray = array(  
            'borders' => array(  
                'allborders' => array(  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框   
                    'color' => array('argb' => '000000'),  
                ),  
            ),  
        );   

        $workSheet->getStyle(sprintf("B2:I%d",$key_1+2))->applyFromArray($styleArray);

        $sheet->getStyle(sprintf("B2:K%d",$key+2))->applyFromArray($styleArray);


        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $file_name = sprintf("%s.xlsx",'财务信息表');
        header("Content-Type: application/force-download"); 
        header("Content-Type: application/octet-stream"); 
        header("Content-Type: application/download"); 
        header('Content-Disposition:inline;filename="'.$file_name.'"'); 
        header("Content-Transfer-Encoding: binary"); 
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
        header("Pragma: no-cache"); 
        $objWriter->save('php://output'); 


    }


}
